0

i need to rename all my sheets dynamically based on a range of cell values. This is my VBA codes, it keeps giving me a 'Runtime Error '1004 whenever i run it. 

Sub RenameSheets()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Config").Range("A5")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("Sheet1").Activate
For Each MyCell In MyRange
ActiveSheet.Name = MyCell.Value 'Error here. it works fine if i rename MyCell.Value to "AnyRandomValue"
Worksheets(ActiveSheet.Index + 1).Select
Next MyCell
End Sub

I cant get my head around it. Why is it giving an error at MyCell.Value? Please help!

Joshua Tan
  • 356
  • 3
  • 4
  • 16

1 Answers1

3

The problem I think is activating the sheets your working on by .Select method.
Avoid using select as much as possible. Check out the link.
Your code can be rewritten like below:

Sub RenameSheets() 
Dim MyNames As Variant
MyRange As Range, ws As Worksheet
Dim i As Long

With Sheets("Config")
    Set MyRange = .Range("A5", .Range("A" & _
        .Rows.Count).End(xlUp).Address)
    '~~> pass names to array
    MyNames = Application.Transpose(MyRange)
    i = Lbound(MyNames)
End With

'~~> iterate sheets instead
For Each ws In Worksheets
    If ws.Name <> "Config" Then
        ws.Name = MyNames(i) '~~> retrieve from array
        i = i + 1
    End If
Next ws

End Sub

Is this what you're trying?

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
  • Wao Yes. Thank you! It works to get what i wanted. didn't thought of doing it this way though. Real neat. Thanks for the link of select. However i still cant wrap my head around my codes. As in what is wrong with it, why is it giving me an error? Am i missing out something? , Even if i remove .Select it would still give me the same error message. – Joshua Tan May 14 '14 at 04:04
  • Even though you've declared your Range Object, the moment you selected another sheet, it looses its reference. Remember, you declared your variable as range, it doesn't include the sheet identity in it. – L42 May 14 '14 at 05:07
  • OH MY! HAHA. THANK YOU so much. Understand clearly now ! – Joshua Tan May 14 '14 at 05:23