Renaming Sheets by appending a string (Excel 2002)

Topic

New Reply

I have a number of workbook with 100 sheets. How do I automate renaming each sheet by adding the same text information at the end of each sheet. That would be the current sheet name + appended text but I want it to be interactive. Run the macro then pause to ask for the string text I want appended then continue renaming the worksheets then saving the workbook with the “original name of the workbook + the appended text.xls”. Any help appreciated.

Reply | Quote

Replies

You could try something like:

Option Explicit

Sub RenameSheets()
Dim ANS As String
Dim Sh As Worksheet

ANS = InputBox(“What is the TEXT STRING you want to add to the sheet names?”)
If Not ANS = “” Then
For Each Sh In Worksheets
Sh.Name = Sh.Name & “-” & ANS
Next Sh
ActiveWorkbook.SaveAs (ActiveWorkbook.Name & “-” & ANS)

End If
End Sub

Reply | Quote

The service in this lounge excellent I might say. Thanks a lot Paul.

Reply | Quote