VBA Code to Get Names from a Tab and Insert New Sheet Between Two Tabs where New Sheet is Name

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance. I will give feedback on whether any proposed solutions work or not.

I have a sheet titled "Menu" and in column B of that sheet I have names of the new Tabs I want to make. The names are in the format "2020.12.30" or "2020.12.31" I want to insert the new tabs between the existing tab "START" and "END." My code is as follows and I'm getting a "compile error" on the following line:

VBA Code:
Sht_Start = Sheets("WL.START").Index

I understand there are far more efficient and advanced ways to write the code, but to ensure I understand it if changes need to be made, please modify my code as little as possible.


VBA Code:
Sub WL_Sheets_Insert()

    Dim LastRowColA As Long
    
    Dim Sht_Start As Sheets
    
    Dim Date1 As String

    Dim i As Long

   'Date1 stores the value in the format "YYYY.MM.DD" such as 2020.12.30, but it's not in an actual date format.
            
    
    '___________________________
        
        Sheets("Menu").Activate
    
        'Sheets(1) = Sheets("START")
    
        LastRowColA = Sheets("WL.Menu").Cells(Rows.Count, 1).End(xlUp).Row
        
        Sht_Start = Sheets("WL.START").Index
    '___________________________
            
            For i = Sht_Start + 1 To LastRowColA
    
                WL_Date = Sheets(Menu).Cells(i, 2).Value
        
                Sheets.Add(After:=Sheets(i - 1)).Name = Date1
        
                Sheets(i).Name = Date1

            Next i


End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
I'm assuming you have a sheet named "Menu".
And in column B of that sheet starting in row 2 you have some dates
Shown like: 2020.12.30

And you want a new sheet created with these dates. And you want these sheets added after the sheet named "Menu"

If all that is true try this script.
Now if your wanting to create new sheets named for every day in the year 2021
You really do not need to have any values in Column B.
Just tell me what is the start date and end date.
Like start with Jan 1 2021 and end with last day in 2021
Or we could even have it skip weekends if you want.
So look at this script.
And we could write in some error catching if this is your ideal.
VBA Code:
Sub Make_New_Sheets()
'Modified  12/31/2020  1:02:48 AM  EST
Application.ScreenUpdating = False
Sheets("Menu").Activate
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Lastrow = Sheets("Menu").Cells(Rows.Count, "B").End(xlUp).Row

With Sheets("Menu")
    For i = 2 To Lastrow
    ans = .Cells(i, 2).Value
        Sheets.Add(After:=Sheets("Menu")).Name = Format(ans, "YYYY.MM.DD")

Next
End With
Application.ScreenUpdating = True
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Now if your wanting to add sheets after sheet named "Start" and before sheet named "End"
That's not something I can do. But there are others on this forum that may have a answer.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Or now this may work. Adds sheets before sheet named End.
VBA Code:
Sub Make_New_Sheets()
'Modified  12/31/2020  1:50:49 AM  EST
Application.ScreenUpdating = False
Sheets("Menu").Activate
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Lastrow = Sheets("Menu").Cells(Rows.Count, "B").End(xlUp).Row

With Sheets("Menu")
    For i = 2 To Lastrow
    ans = .Cells(i, 2).Value
        Sheets.Add(Before:=Sheets("End")).Name = Format(ans, "YYYY.MM.DD")
    Next
End With
Application.ScreenUpdating = True
End Sub
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I'm assuming you have a sheet named "Menu".
And in column B of that sheet starting in row 2 you have some dates
Shown like: 2020.12.30

And you want a new sheet created with these dates. And you want these sheets added after the sheet named "Menu"

If all that is true try this script.
Now if your wanting to create new sheets named for every day in the year 2021
You really do not need to have any values in Column B.
Just tell me what is the start date and end date.
Like start with Jan 1 2021 and end with last day in 2021
Or we could even have it skip weekends if you want.
So look at this script.
And we could write in some error catching if this is your ideal.
VBA Code:
Sub Make_New_Sheets()
'Modified  12/31/2020  1:02:48 AM  EST
Application.ScreenUpdating = False
Sheets("Menu").Activate
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Lastrow = Sheets("Menu").Cells(Rows.Count, "B").End(xlUp).Row

With Sheets("Menu")
    For i = 2 To Lastrow
    ans = .Cells(i, 2).Value
        Sheets.Add(After:=Sheets("Menu")).Name = Format(ans, "YYYY.MM.DD")

Next
End With
Application.ScreenUpdating = True
End Sub
So I used your code and modified it to place the sheet after "Start". I will post the solution if and when I get a chance I need to change it to remove proprietary information which takes some time.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
So I used your code and modified it to place the sheet after "Start". I will post the solution if and when I get a chance I need to change it to remove proprietary information which takes some time.
I really do not need to see your Workbook. I never download files posted on this forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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