Generate and name New workbook + move sheets(That have been generated from template) into the workbook

AppleSource

New Member
Joined
Jan 10, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I found the code quoted Below to generate multiple sheets from a template
ive got that working but id like to be able to also Generate 1x Workbook + name the workbook, then Move(so they would only exsist in new workbook, so not a copy) all the sheets that have been generated into the new workbook(only the generated pages).
Can anyone suggest a simple method of achieving this.

p.s Thanks @jbeaucaire for the awesome code :)
Try this:
Code:
Option Explicit

Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range

With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
   
    Set wsMASTER = .Sheets("Master")                            'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsMASTER.Range("B3:B" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
   
    Application.ScreenUpdating = False                              'speed up macro
    For Each Nm In shNAMES                                          'check one name at a time
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then   'if sheet does not exist...
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)               '...create it from template
            ActiveSheet.Name = CStr(Nm.Text)                        '...rename it
        End If
    Next Nm
   
    wsMASTER.Activate                                           'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end
End With

MsgBox "All sheets created"
End Sub
 

Some videos you may like

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
This code will create the new sheets in a new workbook, you'll need to add code to save the new workbook.
VBA Code:
Option Explicit

Sub SheetsFromTemplate()
Dim wbNew As Workbook
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
    
    With ThisWorkbook                                                   'keep focus in this workbook
        Set wsTEMP = .Sheets("Template")                                'sheet to be copied
        wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)                  'check if it's hidden or not
        If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible          'make it visible
        
        Set wsMASTER = .Sheets("Master")                                'sheet with names
        'range to find names to be checked
        Set shNAMES = wsMASTER.Range("B3:B" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
        
        Application.ScreenUpdating = False                              'speed up macro
        For Each Nm In shNAMES                                          'check one name at a time
            If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then   'if sheet does not exist...
                If wbNew Is Nothing Then
                    wsTEMP.Copy                                         '...create it from template
                    Set wbNew = ActiveWorkbook
                    wbNew.Sheets(1).Name = CStr(Nm.Text)                '...rename it
                Else
                    wsTEMP.Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
                    wbNew.Sheets(wbNew.Sheets.Count).Name = CStr(Nm.Text)
                End If
            End If
        Next Nm
        
        wsMASTER.Activate                                               'return to the master sheet
        If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden           'hide the template if necessary
        Application.ScreenUpdating = True                               'update screen one time at the end
        
    End With
    
    MsgBox "All sheets created"
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,276
Messages
5,600,686
Members
414,400
Latest member
Damocles2021

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