VBA to rename all tabs

J

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
182
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Jmoz092 said:
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
Click to expand...
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
Jmoz092 said:
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]
Click to expand...
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).



Jmoz092 said:
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]
Click to expand...
List is the name of the worksheet containing cell A7.



Jmoz092 said:
[FONT="]
Code: 
Sheets("[COLOR=#ff0000]Sheet[/COLOR]" & Sht).Name = "Data " & wrkYr & wrkMos & [COLOR=#ff0000]Format(Cnt, "00")[/COLOR]
[/FONT]
Click to expand...
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
Fluff said:
Edit.
Rick is obviously a quicker typist than myself
Click to expand...
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 must log in or register to reply here.

Similar threads

M
  • Solved
Find the data and get the cell address
Replies
4
Views
296
Peter_SSs
Peter_SSs
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
179
Peter_SSs
Peter_SSs
H
  • Solved
Copy the first line and paste it at the start of all subsequent sheets in the same tab
Replies
4
Views
190
harzer
H
tezza
  • Solved
INDEX MATCH TWO COLUMNS
Replies
7
Views
358
Peter_SSs
Peter_SSs
H
  • Solved
Delete all spaces in columns "A", "B" and "C".
Replies
6
Views
257
rlv01
R

Forum statistics

Threads
1,216,009
Messages
6,128,258
Members
449,435
Latest member
Jahmia0616

Share this page

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