VBA - Copy template and create new sheets

csimonds

Board Regular
Joined
Oct 2, 2011
Messages
73
Hi,
I have the below code shared by another user that I am using to create new sheets from a list of names in Sheet, 1.EMPLOYEES copying a template sheet, MASTER.
The below code does this well, however I need help to add another step.

In Sheet "1.EMPLOYEES" the name of the sheets is taken from Col. D
When a new sheet is created from the MASTER template and named according to the name in Col. D of sheet 1.EMPLOYEES I would like the value (Employee #) to be copied into cell B3 of the new sheet. Employee # is contained in Col F of sheet 1.EMPLOYEES

The overall logic of the macro is to create a new sheet from a template, name it after the data in Col. D of sheet 1.EMPLOYEES and add the employee number to the new sheet where the employee number is found in Col. F of sheet 1.EMPLOYEES

Any help would be appreciated.
Thanks,

HTML:
Option Explicit
Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range

With ThisWorkbook                                          
Set wsTEMP = .Sheets("MASTER")                             
wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)            
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      

Set wsMASTER = .Sheets("1.EMPLOYEES")            
Set shNAMES = wsMASTER.Range("D2:D" & Rows.Count).SpecialCells(xlConstants)     

Application.ScreenUpdating = False                              
For Each Nm In shNAMES                                          
If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then   
wsTEMP.Copy After:=.Sheets(.Sheets.Count)               
ActiveSheet.Name = CStr(Nm.Text)                       
End If    
Next Nm        

wsMASTER.Activate                                          
If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       
Application.ScreenUpdating = True                         
End With

MsgBox "All sheets created"
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I made some small adjustments to your code, I hope not to confuse you, it would look like this:

Code:
Sub SheetsFromTemplate()
  Dim wsMASTER As Worksheet, wsEMPLOY As Worksheet, NewSheet As Worksheet, Nm As Range
  Application.ScreenUpdating = False
  Set [COLOR=#0000cd]wsMASTER [/COLOR]= Sheets("[COLOR=#0000cd]MASTER[/COLOR]")
  Set [COLOR=#008000]wsEMPLOY [/COLOR]= Sheets("1.[COLOR=#008000]EMPLOYEES[/COLOR]")
  wsMASTER.Visible = xlSheetVisible
  For Each Nm In wsEMPLOY.Range("D2", wsEMPLOY.Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then
      wsMASTER.Copy After:=Sheets(Sheets.Count)
      Set NewSheet = ActiveSheet
      NewSheet.Name = CStr(Nm.Text)
[COLOR=#b22222]      NewSheet.Range("B3") = Nm.Offset(, 1)[/COLOR]
    End If
  Next Nm
  wsMASTER.Visible = xlSheetHidden
  Application.ScreenUpdating = True
  MsgBox "All sheets created"
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

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
Back
Top