Add sheets and rename with direction and current month and year

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
147
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am trying to figure out a way to add new worksheets to a workbook and rename them at the same time. There would be five in total each time the macro is ran for this example. Formatted as such with the Warehouse Name and current Month and Year. The workbook gets saved then new data entered once a month in new sheets if that matters (hate this but that's the way the sales managers want it).

I got as far as Warehouse East but can't figure out how to get the other 4 (West, North South and All). This is what i have so far. I'm sure i am way off base but not finding much via google. Thanks!


VBA Code:
Sub AddSheets_Date()

    Dim TodayDate As String
    TodayDate = Format(Date, "mmmm_yyyy")

    Sheets.Add , Worksheets(Worksheets.Count)
    ActiveSheet.Name = "Warehouse_East_" & TodayDate

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,649
Office Version
  1. 365
Platform
  1. Windows
If you want a different suffix for each sheet, you could create an array like
VBA Code:
Sub Serafin()
   Dim i As Long
   Dim Ary As Variant, Suff As Variant
   Dim Dt As String, ShtName As String
   
   Suff = Array("", "B", "C", "D", "")
   
   Application.ScreenUpdating = False
   Ary = Array("East_", "West_", "North_", "South_", "All_")
   Dt = Format(Date, "mmmm_yyyy")
   
   For i = LBound(Ary) To UBound(Ary)
      ShtName = "Warehouse_" & Ary(i) & Dt & Suff(i)
      If Not Evaluate("isref(" & ShtName & "!A1)") Then
         Sheets.Add(, Sheets(Sheets.Count)).Name = ShtName
      End If
   Next i
   Sheets(Sheets.Count - 4).Select
End Sub
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,186,777
Messages
5,959,724
Members
438,443
Latest member
azedin4

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
Top