Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to name sheets (Excel97 SR2)

    I have an easy one for you (easy for someone). I need a macro to name sheets in a workbook. I am referring to the name that appears on the tab. I have seen this here before but have not able to find it again.
    I have an Excel97 Workbook that is created by an Addin. It is populated with information from a budget database (not Excel). It has over 100 worksheets and the sheets are named by the addin. The names are NOT very meaningful.
    All I want to do is have a macro cycle thru the sheets and rename each sheet (except the first sheet) by taking the first 5 characters of the contents of cell A2 (always cell A2 and each sheet has different contents) and concatenate "Total" to the end.
    If cell A2 of the sheet contains "75030 Rosemount Maintenance" the sheet should be named "75030Total". I create many reports using this addin and it would be nice to be able to easily rename the tabs each time I create a new report.

    Thank you for assisting fellow users thru this forum.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to name sheets (Excel97 SR2)

    assuming that the first sheet is always the first sheet, I believe this will do what you want:

    Sub Macro1()
    Dim i As Integer
    For i = 2 To ActiveWorkbook.Sheets.Count
    Sheets(i).Name = Left(Sheets(i).Cells(2, 1).Value, 5) & "total"
    Next
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to name sheets (Excel97 SR2)

    Brooke,

    Not only fast but accurate! It worked so fast I almost did not realize that it accomplished the task. I thank you.
    I just wish it looked more complicated That was embarrasingly simple. (and I spent about an hour on it and kept failing)
    I LOVE THIS PLACE!

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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