Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Renaming Sheets by appending a string (Excel 2002)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming Sheets by appending a string (Excel 2002)

    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

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming Sheets by appending a string (Excel 2002)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •