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

[OilEconomist]

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:

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.

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

 

[My Aswer Is This]

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.

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]

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]

Or now this may work. Adds sheets before sheet named End.

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]

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.

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]

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.

 

[OilEconomist]

I really do not need to see your Workbook. I never download files posted on this forum.

I wasn't going to upload a file. Just the code.