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

 

[My Aswer Is This]

Try this:

Sub Add_Sheets()
'Modified  11/1/2022  10:04:25 PM  EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Del As Variant
 Del = Array("Warehouse_East_", "Warehouse_West_", "Warehouse_North_", "Warehouse_South_", "All_")

For i = 0 To 4
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Del(i) & Format(Date, "mmmm_yyyy")
Next

Application.ScreenUpdating = True
Exit Sub
M:

MsgBox "That Sheet Name Already Exist"
End Sub

 

[Serafin54]

Try this:

Sub Add_Sheets()
'Modified  11/1/2022  10:04:25 PM  EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Del As Variant
 Del = Array("Warehouse_East_", "Warehouse_West_", "Warehouse_North_", "Warehouse_South_", "All_")

For i = 0 To 4
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Del(i) & Format(Date, "mmmm_yyyy")
Next

Application.ScreenUpdating = True
Exit Sub
M:

MsgBox "That Sheet Name Already Exist"
End Sub

This works well. Thank you. I do have a couple questions on it.

1. I notice that if i run the code a 2nd time, i get the error but a new "sheet" is added. Just curious if there's another way to handle that. The reason being is that I tried the code after I deleted all the new pages, I again got the error but the sheets were added so it's a little confusing to another user.
2. If for any reason the naming of the worksheets needed to be altered so it is "Warehouse_East_December_2022_B"for example is there a way to address that without completely rebuilding the code?

 

[Fluff]

How about

Sub Serafin()
   Dim i As Long
   Dim Ary As Variant
   Dim Dt As String, ShtName As String, Suff As String
   
   Suff = InputBox("Please enter a suffix if needed")
   
   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
      If Not Evaluate("isref(" & ShtName & "!A1)") Then
         Sheets.Add(, Sheets(Sheets.Count)).Name = ShtName
      End If
   Next i
End Sub

This will simply skip any sheets that exist.

 

[Serafin54]

How about

Sub Serafin()
   Dim i As Long
   Dim Ary As Variant
   Dim Dt As String, ShtName As String, Suff As String
  
   Suff = InputBox("Please enter a suffix if needed")
  
   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
      If Not Evaluate("isref(" & ShtName & "!A1)") Then
         Sheets.Add(, Sheets(Sheets.Count)).Name = ShtName
      End If
   Next i
End Sub

This will simply skip any sheets that exist.

Thank you. While that works well also, and it addresses the error item, I was looking to see if i would be able to modify the code to have it hardcoded to be "text_Month_Date_text". This particular report wouldn't need it but in the past it would have come in handy and might rear its head in the future.

Also, I'm trying to figure out how to select the first newly created worksheet when the code is ran. Like is there a way to do Sheets.Activate to go 5 sheets back from the right? If that makes sense.

 

[Fluff]

Ok, how about

Sub Serafin()
   Dim i As Long
   Dim Ary As Variant
   Dim Dt As String, ShtName As String, Suff As String
  
   Suff = ""
  
   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
      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

Just change this line Suff = "" if you want a suffix

 

[My Aswer Is This]

You asked me a question but since I see you have someone else helping you, I will move on and help with a question that has had no help

This works well. Thank you. I do have a couple questions on it.

1. I notice that if i run the code a 2nd time, i get the error but a new "sheet" is added. Just curious if there's another way to handle that. The reason being is that I tried the code after I deleted all the new pages, I again got the error but the sheets were added so it's a little confusing to another user.
2. If for any reason the naming of the worksheets needed to be altered so it is "Warehouse_East_December_2022_B"for example is there a way to address that without completely rebuilding the code?

 

[Serafin54]

You asked me a question but since I see you have someone else helping you, I will move on and help with a question that has had no help

My apologies. I meant no disrespect and my questions still remain. In the same respect I would not ignore anyone that jumps in to throw me a lifeline. Your code is very easy to follow and is a great resource for me to learn and build off of. I do appreciate it and still would like to know if there is a way to make the first added worksheet the active one as well as if there is a way in your code to address the warning.

 

[Serafin54]

Ok, how about

Sub Serafin()
   Dim i As Long
   Dim Ary As Variant
   Dim Dt As String, ShtName As String, Suff As String
 
   Suff = ""
 
   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
      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

Just change this line Suff = "" if you want a suffix

So the only way is to have all or none on the suffix, is that correct? Meaning they all would need to carry the same suffix instead East having A, West being 2, North being Red etc. I was more curious if there would be a wildcard to use in the Array. But as I am speaking, that sounds quite complex.

 

[My Aswer Is This]

My apologies. I meant no disrespect and my questions still remain. In the same respect I would not ignore anyone that jumps in to throw me a lifeline. Your code is very easy to follow and is a great resource for me to learn and build off of. I do appreciate it and still would like to know if there is a way to make the first added worksheet the active one as well as if there is a way in your code to address the warning.

No apology needed but thanks for saying so. I look on the forum each day for questions that have had no answers given and try to help with an answer. But when someone else starts helping with the same question I think Ok why I don't go on and try to help answer another question that has had no answers given. There are a lot of questions here which never get an answer. Many of which I'm not able to help do to lack of knowledge about that subject. As far as to the alert. The script will result in a error if I try to add a sheet with a name that already exist. So a alert pops up saying that sheet name already exist and the script stops. It is true a sheet now exist with no sheet name except for default name. But that should rarely happen. You can just delete that sheet.
As far as activating the first new sheet I see I believe the other poster has provided a answer to that part. I hope you get all the help you need from the other poster here helping you.
One thing I always like to stress is it's great if you can understand most if not all of the code. Hoping someday, you will be able to write code like this for yourself
Like this line of code will take you back to sheet named "Alpha" Range("A1")

Sub Goto_Me()
'Modified 11/2/2022  8:39:46 PM  EST
Application.Goto Sheets("Alpha").Range("A1")
End Sub

You take care and I will continue to watch this thread