Add sheets and rename with direction and current month and year

[Serafin54]

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!

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]

If you want a different suffix for each sheet, you could create an array like

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