Auto generate and name worksheets

[TB_Simonsen]

Hello,

How can I automatically create and name workshets in a workbook based on another 'Master' sheet in the workbook. The list begins at cell A5, and the list lenght will vary from workbook to workbook. Secondly, for each of the automatically created worksheets I need the worksheet name to as listed in cell A but with the additional naming of '_(ID03102011).

I am unaware of whether any existing coding already has been written to perform this task.

TB_Simonsen

 

[bertie]

Hi and welcome to the forum.

I have tested the code below on this sample data in a worksheet named "Master"

Master

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 88px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-WEIGHT: bold">Name List</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>aaa</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>bbb</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>ccc</TD></TR></TBODY></TABLE>


To use the code:
Open a new Excel workbook.
Name Sheet1, "Master".
Insert data for the worksheet name, ensuring no special characters.
Press Alt+F11 to open the VBA Editor.
Click Insert => Module
Copy and paste the code.
Press F8 to step through the code one line at a time or press F5 to run.

[COLOR=darkblue]Sub[/COLOR] InsertSheets()
  [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
  [COLOR=darkblue]Const[/COLOR] id = "_(ID03102011)"
 
  [COLOR=darkblue]Set[/COLOR] rng = Sheets("[COLOR=red]Master[/COLOR]").Range("A5")
  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = rng.Value & id
 
    [COLOR=seagreen]'next row[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
  [COLOR=darkblue]Loop[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[TB_Simonsen]

Hello

Many thanks for your post, and the VB coding works wonderful.

However, is there some coding which will enable the transfer of data in the master spreadsheet, colum m-o, to the automatically created spreadsheets.

TB_Simonsen

 

[bertie]

You may need to edit the pase range.

Sub InsertSheets()
  Dim rng As Range
  [COLOR=black]Dim sName As String[/COLOR]
  Const id = "_(ID03102011)"
 
  Set rng = Sheets("Master").Range("A5")
  Do Until rng = ""
   [COLOR=black]sName[/COLOR] = rng.Value & id
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = [COLOR=black]sName[/COLOR]
    
    'copy and paste
[COLOR=black]   Sheets("Master").Range("M" & rng.Row & ":O" & rng.Row).Copy _[/COLOR]
[COLOR=black]     Destination:=Sheets(sName).Range("[COLOR=red]M2:O2[/COLOR]")
[/COLOR]   
    'next row
    Set rng = rng.Offset(1, 0)
  Loop
End Sub

 

[TB_Simonsen]

Hello

Many thanks for your revised coding. It worked as intended.

TB_Simonsen

 

[chrismb23]

I'm currently using a macro based on the wonderful solution presented here.

I have one sheet with a list of names in colum A, and a country in colum B. On the other sheet I have the template that should be copied on the new sheets. The macro above is working fine, creating a new sheet for every name in the first sheet, with the template copied from the second sheet.

The modification I'm now trying to make is that the macro creates a new workbook (file) for every country listed in colum B of the first sheet, with different sheets created for all names that have that country (based on the template). Would this be possible?

Thanks!

 

[bertie]

Hi, and welcome to the forum.

if you already have a Template file with the necessary worksheets, you can
loop through column B,
open the Template workbook
Save it as the current value

Sub CopyTemplateWorkbook()
   Dim rngCountry As Range
   Dim wbTemplate As Workbook
   
   Set rngCountry = Sheets("Sheet1").Range("B2")
   
   'loop through column B
   Do Until rngCountry = ""
      
      'open the template workbook
      Set wbTemplate = Workbooks.Open("C:\My Documents\Excel\Template.xlsx")
      
      'save the template workbook under the country name in column B
      wbTemplate.SaveAs wbTemplate.Path & "\" & rngCountry.Value & ".xlsx"
      
      'close and save your change
      ActiveWorkbook.Close SaveChanges:=True
   
      Set rngCountry = rngCountry.Offset(1, 0)
   Loop
   
   Set rngCountry = Nothing
End Sub

This version will take longer to run, depending on how many Workbooks/Sheets you have to create.
Loop through column B
Create a new workbook for the current range value
'''loop through column A
'''copy the Template,

Sub CreateWorkbookTemplate()
   Dim wbNew As Workbook
   Dim wsTemplate As Worksheet
   Dim rngNames As Range      'loop variable for column A
   Dim rngCountries As Range  'loop variable for column B
   
   'set an error trap to reset application settings
   On Error GoTo errHandler
   With Application
      .DisplayAlerts = False
      .ScreenUpdating = False
   End With
   
   'set up the Template worksheet
   Set wsTemplate = ThisWorkbook.Sheets("Template")
   
   'set up the range loop varibales
   With ThisWorkbook.Sheets("Sheet1")
      Set rngCountries = .Range("B2")  'assuming header row
      Set rngNames = .Range("A2")
   End With
   
   'loop through the countries until you reach an empty cell
   Do Until rngCountries = ""
      'set up the new workbook
      Workbooks.Add (xlWBATWorksheet)  'add workbook with one sheet
      ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & rngCountries.Value & ".xlsx"
      Set wbNew = ActiveWorkbook
      
      'now loop through column A, add a Template sheet for each name
      Do Until rngNames = ""
         wsTemplate.Copy After:=wbNew.Worksheets(wbNew.Worksheets.Count)
         ActiveSheet.Name = rngNames.Value
         
         'get the next name in column A
         Set rngNames = rngNames.Offset(1, 0)
      Loop
      
      'close and save the new workbook
      wbNew.Close SaveChanges:=True
      Set wbNew = Nothing  'clear the memory
      
      'get the next Workbook name in column B
      Set rngCountries = rngCountries.Offset(1, 0)
      
      'reset the names range to the start of the range
      Set rngNames = ThisWorkbook.Sheets("Sheet1").Range("A2")
   Loop
   
   
errHandler:
   'tidy up
   Set rngCountries = Nothing
   Set rngNames = Nothing
   
   With Application
      .DisplayAlerts = True
      .ScreenUpdating = True
   End With
End Sub

Hope this gives you some ideas.
Bertie

 

[chrismb23]

Many thanks, this is really helpful!

I'm using the second macro you posted on my master workbook. This book contains a worksheet named Master with the names of the persons in colum A and the country in colum B. Furthermore this workbook contains a sheet Template with the template to be copied.

The code is running fine and creates a workbook for every unique country in colum B. However in every country workbook it copies the same persons (everyone) as worksheets. I should only copy the persons for that country.

Am I doing something wrong?

Many thanks,

- Chris