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]
 
Sort by date Sort by votes
Glad we could help & thanks for the feedback
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You must log in or register to reply here.

Similar threads

M
  • Solved
Find the data and get the cell address
Replies
4
Views
281
Peter_SSs
Peter_SSs
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
145
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
168
harzer
H
tezza
  • Solved
INDEX MATCH TWO COLUMNS
Replies
7
Views
341
Peter_SSs
Peter_SSs
H
  • Solved
Delete all spaces in columns "A", "B" and "C".
Replies
6
Views
238
rlv01
R

Forum statistics

Threads
1,215,193
Messages
6,123,560
Members
449,108
Latest member
rache47

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