Change multiple worksheet names based on cell values.

[RobG65]

Hi

Im trying use vba to re name multiple worksheet names in a workbook based on cells in a main sheet in the work book.

Cells in the worksheet main are from C24:C37
Worksheets to rename from Sheet 3 to 17

I assume I need to get the current names of the existing sheets and using that to identify the sheets to rename?


I'm not sure how to go about this any help would be appreciated.

Thank you
Rob

[Benzadeus]

Sub RenameSheets()

Dim n As Integer

With Sheets("Main").Range("C24:C37")
For n = 3 To 16
Sheets("Sheet" & n).Name = .Cells(n)
Next n
End With

End Sub

[RobG65]

Hi Benzadeus,

Thanks for the quick reply, Ive placed the code in the workbook code page unfortunately I get a subscript is out range error.

[Benzadeus]

I'm assuming that the sheets' name are
Sheet1, Sheet2, ..., until Sheet16, in this form.

And in the sheet called "Main", in range
C24 = New name of Sheet1
C25 = New name of Sheet2
...
C37 = New name of Sheet16

Are you testing like that?

[RobG65]

No sorry, they wernt so I renamed them as follows
Sheet3 now named Sheet1
Sheet4 now named Sheet2
Sheet5 now named Sheet3
(confusing I know)

Rerun the script and get a 400 error

What I was hoping to achieve was extracting the current name from the sheets and then renaming to the cells.

[Benzadeus]

Do you want to write each sheets' name in the cells?



Sub RenameSheets()

Dim n As Integer

With Sheets("Main").Range("C24:C37")
For n = 3 To 16
.Cells(n) = Sheets("Sheet" & n).Name
Next n
End With

End Sub

[RobG65]

No, just extract them so I can rename them from the cells.

Thank you

[Benzadeus]

I've read dozen times, but I'm not understanding.

Try doing this way, this will take the 3rd sheet and on, renaming them based on the cells on the range.

Remeber that you can't repeat a sheet name.

Sub RenameSheets()

Dim n As Integer

With Sheets("Main").Range("C24:C37")
For n = 3 To 16
Sheets(n).Name = .Cells(n)
Next n
End With

End Sub

[RobG65]

THis works great to extrcating the existing names thankyou

Sub RenameSheets1()

Dim n As Integer

With Sheets("Main").Range("a24:a37")
For n = 1 To 14
.Cells(n) = Sheets("Sheet" & n).Name
Next n
End With

End Sub
Now those names extracted I want to rename those sheets using the data held in cells c24:c37

I can see were close Sorry if this is confusing!

[Benzadeus]

Sub RenameSheets1()

Dim n As Integer

With Sheets("Main").Range("a24:a37")
For n = 1 To 14
.Cells(n) = Sheets("Sheet" & n).Name
Sheets("Sheet" & n).Name = .Cells(n).Offset(, 2)
Next n
End With

End Sub