VBA code in Podcast 1072-Inserting Multilple Worksheets having names specified in a range

[sajniho]

I liked the video Podcast 1072 of Mr. Excel a lot.

A line of the VBA code

Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr)

is incomplete when the video completes 2 minutes (see just above Bill's pic), Please help by mentioning the complete code of the above line here.

I do not know anything about the VBA. That would be a great help. Thanks in advance.

 

[VoG]

Hello and welcome to MrExcel.

I think it should be

Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr))

 

[sajniho]

I liked the video Podcast 1072 of Mr. Excel a lot.

A line of the VBA code

Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr)

is incomplete when the video completes 2 minutes (see just above Bill's pic), Please help by mentioning the complete code of the above line here.

I do not know anything about the VBA. That would be a great help. Thanks in advance.

Hello and welcome to MrExcel.

I think it should be

Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr))

Hi VoG,

Thank you for the welcome. I had already tried it before posting the query here but the VBA Editor is showing the error in this row. Following is the code that I have typed from the Podcast:

Sub MakeWorkbook()
' code incomplete; Work-in-Progress

    Dim WBT As Workbook
    Dim WBN As Workbook
    Dim WSN As Worksheet
    Dim WST As Worksheet
    
    Set WBT = ActiveWorkbook
    Set WST = ActiveSheet
    
    Ctr = 1
    
    For Each cell In Selection
        If Ctr = 1 Then
            Set WBN = Workbooks.Add(xlWBATWorksheet)
            Set WSN = WBN.Worksheets(1)
        Else
            Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr))
        End If
        On Error Resume Next
        WSN.Name = cell.Value
        On Error GoTo 0
        WSN.Cells(1, 1).Value = cell.Value
        Ctr = Ctr + 1
    Next cell
    
    WBN.Worksheets(1).Select
End Sub

 

[VoG]

Hi. Try this - it works for me:

Sub MakeWorkbook()
' code incomplete; Work-in-Progress

    Dim WBT As Workbook
    Dim WBN As Workbook
    Dim WSN As Worksheet
    Dim WST As Worksheet
    Dim Ctr As Integer '<< added
    Dim cell As Range '<< added
    
    Set WBT = ActiveWorkbook
    Set WST = ActiveSheet
    
    Ctr = 1
    
    For Each cell In Selection
        If Ctr = 1 Then
            Set WBN = Workbooks.Add(xlWBATWorksheet)
            Set WSN = WBN.Worksheets(1)
        Else
            Set WSN = WBN.Worksheets.Add(after:=WSN)
        End If
        On Error Resume Next
        WSN.Name = cell.Value
        On Error GoTo 0
        WSN.Cells(1, 1).Value = cell.Value
        Ctr = Ctr + 1
    Next cell
    
    WBN.Worksheets(1).Select
End Sub

 

[sajniho]

Superb, you made my day!!! Thanks a lot!!!

 

[tonydi]

Incomple line from Podcast 1072 Does anyone know what the rest of the line of code should be without changing the coding.

Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr):


Thanks in advance

 

[VoG]

Try

Set WSN = WBN.Worksheets.Add(after:=WBN.Worksheets(Ctr - 1))

 

[rxp19]

Is it possible to modify this code so that the worksheets are added to the original workbook, rather than creating a new workbook when the macro is ran.

Also, can I assign the macro to use a specific worksheet template when it creates new worksheets?

Thank you!

Hi. Try this - it works for me:

Sub MakeWorkbook()
' code incomplete; Work-in-Progress

    Dim WBT As Workbook
    Dim WBN As Workbook
    Dim WSN As Worksheet
    Dim WST As Worksheet
    Dim Ctr As Integer '<< added
    Dim cell As Range '<< added
    
    Set WBT = ActiveWorkbook
    Set WST = ActiveSheet
    
    Ctr = 1
    
    For Each cell In Selection
        If Ctr = 1 Then
            Set WBN = Workbooks.Add(xlWBATWorksheet)
            Set WSN = WBN.Worksheets(1)
        Else
            Set WSN = WBN.Worksheets.Add(after:=WSN)
        End If
        On Error Resume Next
        WSN.Name = cell.Value
        On Error GoTo 0
        WSN.Cells(1, 1).Value = cell.Value
        Ctr = Ctr + 1
    Next cell
    
    WBN.Worksheets(1).Select
End Sub