title: Copy value form sheet, use as worksheet names (in a loop) - VBA Excel Macro
body: <p>I am attempting to Copy values in a Row, one by one from each cell, then use those values as worksheet names, one by one.  One of the issues I had was skipping the first batch of sheets, so I attempted to hide the ones I don't want renamed.  All other sheets should be renamed (34 of them).  </p>
  
  <p>I can get it all the way "ws.PasteSpecial xlPasteValues" using F8 before I get an error message that says "Run-time error '1004'L Method 'PasteSpecial' of object'_Worksheet' failed.</p>
  
  <p>I have also tried using "Activesheet.PasteSpecial xlPasteValues" but it gave the same error.</p>
  
  <p>Any suggestions at all are very much appreciated, this is driving me nuts. :)  My back up plan is simply using the macro record and doing every rename manually, but it's not a very elegant or simple code that way, so I'd prefer not to do that.</p>
  
  <p>Here is the Code:</p>
  
  <pre><code>Dim ws As Worksheet
  Dim TitleID As String
  Dim TID As String
  
  
  Sheets("SheetName1").Activate
  Set ws = ActiveSheet
  
  Dim rng As Range, cell As Range
  
  Set rng = ws.Range("C5", "AJ5")
  
  For Each cell In rng
      cell.Copy
      Sheets("SheetName1").Visible = False
      ws.Next.Select
      ws.PasteSpecial xlPasteValues
  Next cell
  </code></pre>