Add sheets and rename with direction and current month and year

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
165
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Try this:
VBA Code:
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
 
Upvote 0
Try this:
VBA Code:
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?
 
Upvote 0
How about
VBA Code:
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.
 
Upvote 0
How about
VBA Code:
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.
 
Upvote 0
Ok, how about
Excel Formula:
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
 
Upvote 0
Solution
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?
 
Upvote 0
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.
 
Upvote 0
Ok, how about
Excel Formula:
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.
 
Upvote 0
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")
VBA Code:
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
 
Upvote 0

Forum statistics

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

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