VBA to rename all tabs

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
184
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
  2. MacOS
Hi, I'm having trouble running a code to rename all of the tabs in one of my workbooks. I use this workbook each month, in each year and I need to update it in many different ways to the current month every time we start a new month. So, I'm trying to get the workbook to take care of itself, so to speak. First step, rename all of the tabs to be in line with the current month.

On sheet48, I will have a message box enter data in cell A2, in a standard date format.
Cell A5 displays the month, as a number (mm)
Cell A7 displays the year, as a number (yyyy)
Cell A10 concatenates them (mmyyyy)

The code that I'm trying to run for this is returning a compile error. Where I'm trying to set wrkYr, it highlights "wrkYr" in blue and says object required.

What am I doing wrong??

Code:
[COLOR=#454545][FONT=&quot]Sub ChangeTabNames()[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]'[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]' ChangeTabNames for workbook[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]' Change the numbers of the tabs (20) to the current month[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]'[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Dim Yr As Range[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Dim Mos As Range[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Dim wrkYr As String[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Dim wrkMos As String[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Set Yr = Worksheets("Sheet48").Range("A7")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Set Mos = Worksheets("Sheet48").Range("A5")[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Set wrkYr = Yr.Value[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]Set wrkMos = Mos.Value[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet2").Name = "Data " & wrkYr & wrkMos & "01"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet3").Name = "Invoice " & wrkYr & wrkMos & "01"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet4").Name = "Data " & wrkYr & wrkMos & "02"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet5").Name = "Invoice " & wrkYr & wrkMos & "02"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet6").Name = "Data " & wrkYr & wrkMos & "03"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet7").Name = "Invoice " & wrkYr & wrkMos & "03"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet8").Name = "Data " & wrkYr & wrkMos & "04"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet9").Name = "Invoice " & wrkYr & wrkMos & "04"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet10").Name = "Data " & wrkYr & wrkMos & "05"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet11").Name = "Invoice " & wrkYr & wrkMos & "05"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet12").Name = "Data " & wrkYr & wrkMos & "06"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet13").Name = "Invoice " & wrkYr & wrkMos & "06"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet14").Name = "Data " & wrkYr & wrkMos & "07"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet15").Name = "Invoice " & wrkYr & wrkMos & "07"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet16").Name = "Data " & wrkYr & wrkMos & "08"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet17").Name = "Invoice " & wrkYr & wrkMos & "08"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet18").Name = "Data " & wrkYr & wrkMos & "09"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet19").Name = "Invoice " & wrkYr & wrkMos & "09"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet20").Name = "Data " & wrkYr & wrkMos & "10"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet21").Name = "Invoice " & wrkYr & wrkMos & "10"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet22").Name = "Data " & wrkYr & wrkMos & "011"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet23").Name = "Invoice " & wrkYr & wrkMos & "011"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet24").Name = "Data " & wrkYr & wrkMos & "012"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet25").Name = "Invoice " & wrkYr & wrkMos & "012"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet26").Name = "Data " & wrkYr & wrkMos & "013"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet27").Name = "Invoice " & wrkYr & wrkMos & "013"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet28").Name = "Data " & wrkYr & wrkMos & "014"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet29").Name = "Invoice " & wrkYr & wrkMos & "014"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet30").Name = "Data " & wrkYr & wrkMos & "015"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet31").Name = "Invoice " & wrkYr & wrkMos & "015"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet32").Name = "Data " & wrkYr & wrkMos & "016"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet33").Name = "Invoice " & wrkYr & wrkMos & "016"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet34").Name = "Data " & wrkYr & wrkMos & "017"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet35").Name = "Invoice " & wrkYr & wrkMos & "017"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet36").Name = "Data " & wrkYr & wrkMos & "018"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet37").Name = "Invoice " & wrkYr & wrkMos & "018"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet38").Name = "Data " & wrkYr & wrkMos & "019"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet39").Name = "Invoice " & wrkYr & wrkMos & "019"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet40").Name = "Data " & wrkYr & wrkMos & "020"[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("Sheet41").Name = "Invoice " & wrkYr & wrkMos & "020"
         [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]End Sub[/FONT][/COLOR]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What am I doing wrong??

Code:
Sub ChangeTabNames()
'
' ChangeTabNames for workbook
' Change the numbers of the tabs (20) to the current month
'
Dim Yr As Range
Dim Mos As Range
[B][COLOR="#FF0000"]Dim wrkYr As String
Dim wrkMos As String[/COLOR][/B]

Set Yr = Worksheets("Sheet48").Range("A7")
Set Mos = Worksheets("Sheet48").Range("A5")

[B][COLOR="#FF0000"]Set[/COLOR][/B] wrkYr = Yr.Value
[B][COLOR="#FF0000"]Set[/COLOR][/B] wrkMos = Mos.Value
You declared the highlighted variables as String... you do not use Set with normal (non-object) variables like String.
 
Last edited:
Upvote 0
You want to name sheet(1) to "20"

Data 12201701
Invoice 12201701

Data 12201702
Invoice 12201702

etc.
Is this correct?
You said:
Change the numbers of the tabs (20)

But it looks like you have 40 tabs
 
Last edited:
Upvote 0
@My Aswer Is This
Yes, 40 tabs, but there are 20 "instances". Each "instance" has a data tab and an invoice tab. That wording is just a matter of trying to keep it simple enough for the user, but descriptive enough for the developers, sorry.
@Rick Rothstein
Thanks for pointing that out to me. I was able to get the code to work and rename all of the tabs as intended with the code listed below.

For Rick Rothstein or My Aswer Is This,
Is this the "correct" way to do (Dim or declare) this???

Code:
Sub ChangeTabNames()
'
' ChangeTabNames for workbook
' Change the numbers of the tabs (20) to the current month
'


Dim Yr As Range
Dim Mos As Range
Dim wrkYr As String
Dim wrkMos As String


Set Yr = Sheet48.Range("A7")
Set Mos = Sheet48.Range("A5")


wrkYr = Sheet48.Range("A7").Value
wrkMos = Sheet48.Range("A5").Value




    Sheet2.Name = "Data " & wrkYr & wrkMos & "01"
    Sheet3.Name = "Invoice " & wrkYr & wrkMos & "01"


    Sheet4.Name = "Data " & wrkYr & wrkMos & "02"
    Sheet5.Name = "Invoice " & wrkYr & wrkMos & "02"
    
    Sheet6.Name = "Data " & wrkYr & wrkMos & "03"
    Sheet7.Name = "Invoice " & wrkYr & wrkMos & "03"
    
    Sheet8.Name = "Data " & wrkYr & wrkMos & "04"
    Sheet9.Name = "Invoice " & wrkYr & wrkMos & "04"
    
    Sheet10.Name = "Data " & wrkYr & wrkMos & "05"
    Sheet11.Name = "Invoice " & wrkYr & wrkMos & "05"
    
    Sheet12.Name = "Data " & wrkYr & wrkMos & "06"
    Sheet13.Name = "Invoice " & wrkYr & wrkMos & "06"
    
    Sheet14.Name = "Data " & wrkYr & wrkMos & "07"
    Sheet15.Name = "Invoice " & wrkYr & wrkMos & "07"
    
    Sheet16.Name = "Data " & wrkYr & wrkMos & "08"
    Sheet17.Name = "Invoice " & wrkYr & wrkMos & "08"
    
    Sheet18.Name = "Data " & wrkYr & wrkMos & "09"
    Sheet19.Name = "Invoice " & wrkYr & wrkMos & "09"
    
    Sheet20.Name = "Data " & wrkYr & wrkMos & "10"
    Sheet21.Name = "Invoice " & wrkYr & wrkMos & "10"
    
    Sheet22.Name = "Data " & wrkYr & wrkMos & "011"
    Sheet23.Name = "Invoice " & wrkYr & wrkMos & "011"
    
    Sheet24.Name = "Data " & wrkYr & wrkMos & "012"
    Sheet25.Name = "Invoice " & wrkYr & wrkMos & "012"
    
    Sheet26.Name = "Data " & wrkYr & wrkMos & "013"
    Sheet27.Name = "Invoice " & wrkYr & wrkMos & "013"
    
    Sheet28.Name = "Data " & wrkYr & wrkMos & "014"
    Sheet29.Name = "Invoice " & wrkYr & wrkMos & "014"
    
    Sheet30.Name = "Data " & wrkYr & wrkMos & "015"
    Sheet31.Name = "Invoice " & wrkYr & wrkMos & "015"
    
    Sheet32.Name = "Data " & wrkYr & wrkMos & "016"
    Sheet33.Name = "Invoice " & wrkYr & wrkMos & "016"
    
    Sheet34.Name = "Data " & wrkYr & wrkMos & "017"
    Sheet35.Name = "Invoice " & wrkYr & wrkMos & "017"
    
    Sheet36.Name = "Data " & wrkYr & wrkMos & "018"
    Sheet37.Name = "Invoice " & wrkYr & wrkMos & "018"
    
    Sheet38.Name = "Data " & wrkYr & wrkMos & "019"
    Sheet39.Name = "Invoice " & wrkYr & wrkMos & "019"
    
    Sheet40.Name = "Data " & wrkYr & wrkMos & "020"
    Sheet41.Name = "Invoice " & wrkYr & wrkMos & "020"




    
            
End Sub
 
Last edited:
Upvote 0
Your declarations are fine. Although you can do away with the ranges & assign values straight to the variable (as below)
Here is a slightly shorter version of your code, if you're interested
Code:
Sub ChangeTabNames()
'
' ChangeTabNames for workbook
' Change the numbers of the tabs (20) to the current month
'


    Dim wrkYr As String
    Dim wrkMos As String
    Dim Cnt As Long
    Dim Sht As Long
    
    wrkYr = Worksheets("List").Range("A7").Value
    wrkMos = Worksheets("List").Range("A5").Value
    
    Cnt = 1
    For Sht = 2 To 41 Step 2
        Sheets("Sheet" & Sht).Name = "Data " & wrkYr & wrkMos & Format(Cnt, "00")
        Sheets("Sheet" & Sht + 1).Name = "Invoice " & wrkYr & wrkMos & Format(Cnt, "00")
        Cnt = Cnt + 1
    Next Sht
End Sub
 
Last edited:
Upvote 0
Thanks @Fluff, couple questions...


what does this mean:
Code:
[COLOR=#454545][FONT=&quot][SIZE=3]    Dim Cnt As Long[/SIZE][/FONT][/COLOR]
[SIZE=3][/SIZE][COLOR=#454545][FONT=&quot][SIZE=3]    Dim Sht As Long[/SIZE][/FONT][/COLOR]


What does the red mean in these
Code:
[COLOR=#454545][FONT=&quot][SIZE=3]wrkYr = Worksheets("[COLOR=#ff0000]List[/COLOR]").Range("A7").Value[/SIZE][/FONT][/COLOR]

[FONT=&quot]
Code:
Sheets("[COLOR=#ff0000]Sheet[/COLOR]" & Sht).Name = "Data " & wrkYr & wrkMos & [COLOR=#ff0000]Format(Cnt, "00")[/COLOR]

As always, thanks to everyone for helping me learn VBA!
[/FONT]
 
Upvote 0
what does this mean:
Code:
[COLOR=#454545][FONT="][SIZE=3]    Dim Cnt As Long[/SIZE][/FONT][/COLOR]
[COLOR=#454545][FONT="][SIZE=3]    Dim Sht As Long[/SIZE][/FONT][/COLOR]
These lines of code declare the variables as Long. Long is the same as Integer, but can handle much larger numbers. My recommendation is to always use Long for your non-floating point numbers and never use Integer unless your variable will be used when calling someone else's function whose argument is declared as Integer (something you will not have to worry about at your level of experience with VBA).



What does the red mean in these
Code:
[COLOR=#454545][FONT="][SIZE=3]wrkYr = Worksheets("[COLOR=#ff0000]List[/COLOR]").Range("A7").Value[/SIZE][/FONT][/COLOR]
List is the name of the worksheet containing cell A7.



[FONT="]
Code:
Sheets("[COLOR=#ff0000]Sheet[/COLOR]" & Sht).Name = "Data " & wrkYr & wrkMos & [COLOR=#ff0000]Format(Cnt, "00")[/COLOR]
[/FONT]
Sheet is just text... it is the beginning of the worksheet's name and it will be concatenated with the value the Sht variable contains when it is executed. So, if Sht equals 5, then the sheet whose name is Sheet5 will be referenced when this code line is executed.
 
Upvote 0
Code:
[COLOR=#454545][FONT='inherit'][SIZE=3]  Dim Cnt As Long[/SIZE][/FONT][/COLOR]
[COLOR=#454545][FONT='inherit'][SIZE=3]    Dim Sht As Long[/SIZE][/FONT][/COLOR]
This declares the 2 variables as a Long Integer. See here for more info
http://www.informit.com/articles/article.aspx?p=339929&seqNum=2
Code:
[COLOR=#454545][FONT='inherit'][SIZE=3]wrkYr = Worksheets("[COLOR=#ff0000]List[/COLOR]").Range("A7").Value[/SIZE][/FONT][/COLOR]
"List" should be "Sheet48" I changed it for testing & forgot to change it back. :oops:
Code:
[COLOR=#454545][FONT='inherit'][SIZE=3]Sheets("[COLOR=#ff0000]Sheet[/COLOR]" & Sht)[/SIZE][/FONT][/COLOR]
This is a concatenation of sheet and Sht, giving the sheet name.
Sht starts at 2 so the first time through the loop, this gives Sheet & 2, or Sheet2.
Code:
[COLOR=#454545][FONT='inherit'][SIZE=3][COLOR=#ff0000]Format(Cnt, "00")[/COLOR][/SIZE][/FONT][/COLOR]
this formats the Cnt variable to return 2 digit. So if Cnt is 1, this will give 01

HTH


Edit.
Rick is obviously a quicker typist than myself
 
Last edited:
Upvote 0
Edit.
Rick is obviously a quicker typist than myself
Yeah, but I did not address your misuse of List for Sheet48 which probably was at the heart of the OP's query. Also, I forgot to address the Format function part of the OP's query. So, with so much less to type, no wonder I was able to finish my typing before you.:eek:
 
Last edited:
Upvote 0
You guys/gals are amazing. Thank you very much for the explanations and references!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top