Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to change Excel sheet names (XP)

    If this is in a single workbook, I would suggest that it will be just as easy to do it manually. Right click the worksheet tab and select "Rename".
    Regards
    Don

  2. #2
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to change Excel sheet names (XP)

    I have a spreadsheet with worksheet tabs labelled Jan06, Feb06, etc. Is it possble to run VBA that will change each sheet so they end in 08?

    Thanks.

    Using the Macro record feature this is as far as I got.

    Sub ChangeTabNames ()
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets(Array(“Jan06”, “Feb06”, “Mar06”, “Apr06”, “May06”, “Jun06”, “Jul06”, “Aug06”, “Sept06”, “Oct06”, “Nov06”, “Dec06”)).Select
    Sheets(“Dec06”).Activate
    End Sub

    The array came about by selecting the first tab and shift to last tab.

    My problem arises with trying to change all tab names. Not sure where to go from here

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to change Excel sheet names (XP)

    I realize that the rename feature is there. But as I am trying to learn coding I thought that this might be a practical way of learning as I use tab names often.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to change Excel sheet names (XP)

    The following will loop through all work sheets and change the last two characters to 08.

    Sub change()
    Dim i As Integer
    For i = 1 To Worksheets.Count
    Worksheets(i).Name = Left(Worksheets(i).Name, Len(Worksheets(i).Name) - 2) & "08"
    Next
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to change Excel sheet names (XP)

    Thanks so much.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA to change Excel sheet names (XP)

    Since you are trying to learn, here's an alternative, that only replaces 06 with 08 (In case you have other sheets you don't want renamed) and uses a different type of loop:
    <pre>Sub change()
    Dim wks as Worksheet
    For Each wks in ActiveWorkbook.Worksheets
    wks.Name = Replace$(wks.Name, "06", "08")
    Next wks
    End Sub
    </pre>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to change Excel sheet names (XP)

    Thanks.

Posting Permissions

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