Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Renaming all tabs in a spreadsheet and clearing their entries - Excel 2010

    I have a spreadsheet with 13 tabs in it. The first tab is a Yearly summary of the months on each of the other 12 tabs. I want to be able to re-use the spreadsheet for 2014 with going to all the trouble of renaming every tab, i.e., updating the year to be 2014 instead of 2013 and I also want to clear all of the data on each of the spreadsheet's tabs so that they can be reused. Is there a way to "mass rename" all of the tabs, or at least change the reference to 2013 to 2014 and also to clear all the entries from the 12 monthly tabs? I guess this would be something like a "universal" seek and destroy or change. Any insight that anyone would care to share will be greatly appreciated. Thanks.

    Ron M

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I also want to clear all of the data on each of the spreadsheet's tabs so that they can be reused.
    Ron,

    I am guessing that you want to clear each sheet as well as rename the sheet tabs.

    HTH,
    Maud

    months.png

    Code:
    Public Sub Cleartabs()
    Worksheets(1).Name = "2014 Summary"
    Worksheets(1).Cells.ClearContents
    For I = 2 To Worksheets.Count
        Worksheets(I).Name = MonthName(I - 1) & " 2014"
        Worksheets(I).Cells.ClearContents
    Next I
    End Sub
    Last edited by Maudibe; 2014-01-09 at 17:09.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Thanks Maud. I am not a VBA person, so your help is appreciated. So the question now is how do I incorporate this macro into the spreadsheet. I am assuming that I can copy and paste it somehow, or do I have to record it in the appropriate spreadsheet? Well, not to worry, I figured it out and it ran and it worked, so again, thank you very much.

    Ron M

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ron,

    Not to undercut Maud's excellent work but have you considered just deleting the Year from the tabs and then incorporating the year in the name of the workbook? With this approach you could create a blank workbook with all 13 sheets and then just save it as a template and create a new workbook from it each year. You could also integrate the Year into the Header/Footer by striping it from the file name or just incorporate the whole name into the Header/Footer. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Kind of along the same lines as RG's idea, but not as a template, you can keep the year in the sheet name with the following code:

    Place in This Workbook Module:
    Code:
    Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    wbYear = Left(ThisWorkbook.Name, 4)
    For I = 1 To Worksheets.Count
        s = Split(Worksheets(I).Name, " ")
        Worksheets(I).Name = s(0) & " " & wbYear
        Worksheets(I).Cells.ClearContents
    Next I
    End Sub
    After opening a used workbook, you can do a File>Save As and save it as "year Summary". The code will immediately extract the year from the name and add it to all the sheet names as well as clear any data from the sheets. In this example, you open a used workbook called 2013 Summary and save it as 2014 Summary. The book immediately turns into a new workbook with clear sheets and the tabs appropriately named with the month and 2014.

    Summary1.png

    Summary2.png
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Quote Originally Posted by RetiredGeek View Post
    Ron,

    Not to undercut Maud's excellent work but have you considered just deleting the Year from the tabs and then incorporating the year in the name of the workbook? With this approach you could create a blank workbook with all 13 sheets and then just save it as a template and create a new workbook from it each year. You could also integrate the Year into the Header/Footer by striping it from the file name or just incorporate the whole name into the Header/Footer. HTH
    RG, thanks for the idea. I had forgotten that you could create "templates" for Excel spreadsheets. I used to do it all the time for Word documents when I was consulting. I had templates for an entire project document system that I would carry around with me from client to client and it worked like a charm - impressed the he!! out of the client as well.

    Ron M

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Maud, thanks for the update. I will definitely give this one a look. The first code SUB that you gave me worked like a charm, so I will have to think about changing.

    Ron M

Posting Permissions

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