Creating a macro to rename worksheet tabs every month

V

vickmoody

New Member
Joined
Jun 7, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

I create a team tracking sheet where everyone enters their numbers every month. Now, I need to create a macro that will rename the sheets automatically based a range of cells. Instead of renaming the tabs one by one. The values are dates which I want the tabs formatted with "mm.dd". I also need the macro to read the range of cells that have dates for everyday of the month excluding weekends and holidays.

I have attached the spreadsheet that is created and I need the tabs to have the dates from worksheet Daily Totals from A4:A27. And if there are left over worksheets, it needs to be hidden.


Team tracking master.xlsm
ABCDEFGHI
3DateRFA FUNDINGDelayed Invoice FlooringCommitmentsInvoice PrepNext DayTransfersExtensionsTotal Email Count
49/1/202100000000
59/2/202100000000
69/3/202100000000
79/7/202100000000
89/8/202100000000
99/9/202100000000
109/10/202100000000
119/13/202100000000
129/14/202100000000
139/15/202100000000
149/16/202100000000
159/17/202100000000
169/20/202100000000
179/21/202100000000
189/22/202100000000
199/23/202100000000
209/24/202100000000
219/27/202100000000
229/28/202100000000
239/29/202100000000
249/30/202100000000
2500000000
2600000000
27
28TOTALS:00000000
Daily Totals
Cell Formulas
RangeFormula
B4B4=Sheet1!B48
C4C4=Sheet1!B46
D4D4=Sheet1!B47
E4E4=Sheet1!B45
F4F4=Sheet1!B51
G4G4=Sheet1!B54
H4H4=Sheet1!B53
I4I4=Sheet1!B26
B5B5=Sheet2!B48
C5C5=Sheet2!B46
D5D5=Sheet2!B47
E5E5=Sheet2!B45
F5F5=Sheet2!B51
G5G5=Sheet2!B54
H5H5=Sheet2!B53
I5I5=Sheet2!B26
B6B6=Sheet3!B48
C6C6=Sheet3!B46
D6D6=Sheet3!B47
E6E6=Sheet3!B45
F6F6=Sheet3!B51
G6G6=Sheet3!B54
H6H6=Sheet3!B53
I6I6=Sheet3!B26
B7B7=Sheet4!B48
C7C7=Sheet4!B46
D7D7=Sheet4!B47
E7E7=Sheet4!B45
F7F7=Sheet4!B51
G7G7=Sheet4!B54
H7H7=Sheet4!B53
I7I7=Sheet4!B26
B8B8=Sheet5!B48
C8C8=Sheet5!B46
D8D8=Sheet5!B47
E8E8=Sheet5!B45
F8F8=Sheet5!B51
G8G8=Sheet5!B54
H8H8=Sheet5!B53
I8I8=Sheet5!B26
B9B9=Sheet6!B48
C9C9=Sheet6!B46
D9D9=Sheet6!B47
E9E9=Sheet6!B45
F9F9=Sheet6!B51
G9G9=Sheet6!B54
H9H9=Sheet6!B53
I9I9=Sheet6!B26
B10B10=Sheet7!B48
C10C10=Sheet7!B46
D10D10=Sheet7!B47
E10E10=Sheet7!B45
F10F10=Sheet7!B51
G10G10=Sheet7!B54
H10H10=Sheet7!B53
I10I10=Sheet7!B26
B11B11=Sheet8!B48
C11C11=Sheet8!B46
D11D11=Sheet8!B47
E11E11=Sheet8!B45
F11F11=Sheet8!B51
G11G11=Sheet8!B54
H11H11=Sheet8!B53
I11I11=Sheet8!B26
B12B12=Sheet9!B48
C12C12=Sheet9!B46
D12D12=Sheet9!B47
E12E12=Sheet9!B45
F12F12=Sheet9!B51
G12G12=Sheet9!B54
H12H12=Sheet9!B53
I12I12=Sheet9!B26
B13B13=Shee10!B48
C13C13=Shee10!B46
D13D13=Shee10!B47
E13E13=Shee10!B45
F13F13=Shee10!B51
G13G13=Shee10!B54
H13H13=Shee10!B53
I13I13=Shee10!B26
B14B14=Sheet11!B48
C14C14=Sheet11!B46
D14D14=Sheet11!B47
E14E14=Sheet11!B45
F14F14=Sheet11!B51
G14G14=Sheet11!B54
H14H14=Sheet11!B53
I14I14=Sheet11!B26
B15B15=Sheet12!B48
C15C15=Sheet12!B46
D15D15=Sheet12!B47
E15E15=Sheet12!B45
F15F15=Sheet12!B51
G15G15=Sheet12!B54
H15H15=Sheet12!B53
I15I15=Sheet12!B26
B16B16=Sheet13!B48
C16C16=Sheet13!B46
D16D16=Sheet13!B47
E16E16=Sheet13!B45
F16F16=Sheet13!B51
G16G16=Sheet13!B54
H16H16=Sheet13!B53
I16I16=Sheet13!B26
B17B17=Sheet14!B48
C17C17=Sheet14!B46
D17D17=Sheet14!B47
E17E17=Sheet14!B45
F17F17=Sheet14!B51
G17G17=Sheet14!B54
H17H17=Sheet14!B53
I17I17=Sheet14!B26
B18B18=Sheet15!B48
C18C18=Sheet15!B46
D18D18=Sheet15!B47
E18E18=Sheet15!B45
F18F18=Sheet15!B51
G18G18=Sheet15!B54
H18H18=Sheet15!B53
I18I18=Sheet15!B26
B19B19=Sheet16!B48
C19C19=Sheet16!B46
D19D19=Sheet16!B47
E19E19=Sheet16!B45
F19F19=Sheet16!B51
G19G19=Sheet16!B54
H19H19=Sheet16!B53
I19I19=Sheet16!B26
B20B20=Sheet17!B48
C20C20=Sheet17!B46
D20D20=Sheet17!B47
E20E20=Sheet17!B45
F20F20=Sheet17!B51
G20G20=Sheet17!B54
H20H20=Sheet17!B53
I20I20=Sheet17!B26
B21B21=Sheet18!B48
C21C21=Sheet18!B46
D21D21=Sheet18!B47
E21E21=Sheet18!B45
F21F21=Sheet18!B51
G21G21=Sheet18!B54
H21H21=Sheet18!B53
I21I21=Sheet18!B26
B22B22=Sheet19!B48
C22C22=Sheet19!B46
D22D22=Sheet19!B47
E22E22=Sheet19!B45
F22F22=Sheet19!B51
G22G22=Sheet19!B54
H22H22=Sheet19!B53
I22I22=Sheet19!B26
B23B23=Sheet20!B48
C23C23=Sheet20!B46
D23D23=Sheet20!B47
E23E23=Sheet20!B45
F23F23=Sheet20!B51
G23G23=Sheet20!B54
H23H23=Sheet20!B53
I23I23=Sheet20!B26
B24B24=Sheet21!B48
C24C24=Sheet21!B46
D24D24=Sheet21!B47
E24E24=Sheet21!B45
F24F24=Sheet21!B51
G24G24=Sheet21!B54
H24H24=Sheet21!B53
I24I24=Sheet21!B26
B25B25=Sheet22!B48
C25C25=Sheet22!B46
D25D25=Sheet22!B46
E25E25=Sheet22!B45
F25F25=Sheet22!B51
G25G25=Sheet22!B54
H25H25=Sheet22!B53
I25I25=Sheet22!B26
B26B26=Sheet23!B49
C26C26=Sheet23!B47
D26D26=Sheet23!B47
E26E26=Sheet23!B46
F26F26=Sheet23!B52
G26G26=Sheet23!B55
H26H26=Sheet23!B54
I26I26=Sheet23!B27
A5:A24A5=WORKDAY(A4,1,$A$1)
B28:I28B28=SUM(B4:B27)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Try:

VBA Code: 
Sub AddSheets()

Dim myRng As Range
Dim myDate As Range

Set myRng = Sheets("Daily Totals").Range("A4:A24")

For Each cell In myRng
If Weekday(cell.Value) <> 1 And Weekday(cell.Value) <> 7 Then
    Sheets.Add
    ActiveSheet.Name = Format(cell.Value, "MM.DD")
End If
Next cell

End Sub
 
Upvote 0
Thank you for creating the VB code, however, i forgot to mention that I have tabs that are already named like Sheet1, Sheet2, Sheet3 etc..etc.. I need to have the code rename those sheets as there is data in it. The tabs are renamed every month with the dates.
 
Upvote 0
Hello, Can anyone help me on creating a code with existing worksheet tabs using the cell value?
 
Upvote 0
A little crude, but this should rename sheets with names starting with "Sheet"

VBA Code: 
Sub AddSheets()

Dim myRng As Range
Dim myDate As Range
Dim ws as worksheet 

Set myRng = Sheets("Daily Totals").Range("A4:A24")

For Each cell In myRng
If Weekday(cell.Value) <> 1 And Weekday(cell.Value) <> 7 Then
    For Each ws in thisworkbook.worksheets 
    If ws.name like ("Sheet*") then
    ActiveSheet.Name = Format(cell.Value, "MM.DD")
    Next ws
End If
Next cell

End Sub
 
Upvote 0
Forgot to end the if

VBA Code: 
Sub AddSheets()

Dim myRng As Range
Dim myDate As Range
Dim ws as worksheet 

Set myRng = Sheets("Daily Totals").Range("A4:A24")

For Each cell In myRng
If Weekday(cell.Value) <> 1 And Weekday(cell.Value) <> 7 Then
    For Each ws in thisworkbook.worksheets 
    If ws.name like ("Sheet*") then
    ActiveSheet.Name = Format(cell.Value, "MM.DD")
    End if
    Next ws
End If
Next cell

End Sub
 
Upvote 0
@mrshl9898:

This code just renamed the current sheet Daily Totals to 10.29. Not all the sheets. We are not adding sheets, we are naming the existing sheets. The existing sheets have data in it, so all I need is to rename them to the dates that range A4:A23. Thank you.

Thank you.

1623590114290.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Simplify VBA code
Replies
2
Views
433
kevin9999
K
K
  • Question Question
Combine Multiple Excel Tables
Replies
4
Views
618
Kalim Shaikh
K
U
  • Question Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
644
Alex Blakenburg
A
M
  • Solved
Clear content in multiple ranges on 14 different worksheets.
Replies
7
Views
768
MAP77
M
M
  • Question Question
VBA Sorting Column Issue
Replies
0
Views
470
mrstats_2288
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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