Jmoz092
Board Regular
- Joined
- Sep 8, 2017
- Messages
- 184
- Office Version
- 365
- 2011
- Platform
- Windows
- 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??
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="]Sub ChangeTabNames()[/FONT][/COLOR]
[COLOR=#454545][FONT="]'[/FONT][/COLOR]
[COLOR=#454545][FONT="]' ChangeTabNames for workbook[/FONT][/COLOR]
[COLOR=#454545][FONT="]' Change the numbers of the tabs (20) to the current month[/FONT][/COLOR]
[COLOR=#454545][FONT="]'[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="]Dim Yr As Range[/FONT][/COLOR]
[COLOR=#454545][FONT="]Dim Mos As Range[/FONT][/COLOR]
[COLOR=#454545][FONT="]Dim wrkYr As String[/FONT][/COLOR]
[COLOR=#454545][FONT="]Dim wrkMos As String[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="]Set Yr = Worksheets("Sheet48").Range("A7")[/FONT][/COLOR]
[COLOR=#454545][FONT="]Set Mos = Worksheets("Sheet48").Range("A5")[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="]Set wrkYr = Yr.Value[/FONT][/COLOR]
[COLOR=#454545][FONT="]Set wrkMos = Mos.Value[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet2").Name = "Data " & wrkYr & wrkMos & "01"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet3").Name = "Invoice " & wrkYr & wrkMos & "01"[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet4").Name = "Data " & wrkYr & wrkMos & "02"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet5").Name = "Invoice " & wrkYr & wrkMos & "02"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet6").Name = "Data " & wrkYr & wrkMos & "03"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet7").Name = "Invoice " & wrkYr & wrkMos & "03"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet8").Name = "Data " & wrkYr & wrkMos & "04"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet9").Name = "Invoice " & wrkYr & wrkMos & "04"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet10").Name = "Data " & wrkYr & wrkMos & "05"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet11").Name = "Invoice " & wrkYr & wrkMos & "05"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet12").Name = "Data " & wrkYr & wrkMos & "06"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet13").Name = "Invoice " & wrkYr & wrkMos & "06"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet14").Name = "Data " & wrkYr & wrkMos & "07"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet15").Name = "Invoice " & wrkYr & wrkMos & "07"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet16").Name = "Data " & wrkYr & wrkMos & "08"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet17").Name = "Invoice " & wrkYr & wrkMos & "08"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet18").Name = "Data " & wrkYr & wrkMos & "09"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet19").Name = "Invoice " & wrkYr & wrkMos & "09"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet20").Name = "Data " & wrkYr & wrkMos & "10"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet21").Name = "Invoice " & wrkYr & wrkMos & "10"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet22").Name = "Data " & wrkYr & wrkMos & "011"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet23").Name = "Invoice " & wrkYr & wrkMos & "011"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet24").Name = "Data " & wrkYr & wrkMos & "012"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet25").Name = "Invoice " & wrkYr & wrkMos & "012"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet26").Name = "Data " & wrkYr & wrkMos & "013"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet27").Name = "Invoice " & wrkYr & wrkMos & "013"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet28").Name = "Data " & wrkYr & wrkMos & "014"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet29").Name = "Invoice " & wrkYr & wrkMos & "014"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet30").Name = "Data " & wrkYr & wrkMos & "015"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet31").Name = "Invoice " & wrkYr & wrkMos & "015"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet32").Name = "Data " & wrkYr & wrkMos & "016"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet33").Name = "Invoice " & wrkYr & wrkMos & "016"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet34").Name = "Data " & wrkYr & wrkMos & "017"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet35").Name = "Invoice " & wrkYr & wrkMos & "017"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet36").Name = "Data " & wrkYr & wrkMos & "018"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet37").Name = "Invoice " & wrkYr & wrkMos & "018"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet38").Name = "Data " & wrkYr & wrkMos & "019"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet39").Name = "Invoice " & wrkYr & wrkMos & "019"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet40").Name = "Data " & wrkYr & wrkMos & "020"[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("Sheet41").Name = "Invoice " & wrkYr & wrkMos & "020"
[/FONT][/COLOR]
[COLOR=#454545][FONT="]End Sub[/FONT][/COLOR]