VBA - Copy and create identical sheets except name of sheet.

[X_Nemo]

Hi Forum,

It's a pleasure joining your forum and I hope that you can answer my question or atleast give some input.

I need to make a lot of sheets with different name but the same functions and formating.

Let me try to split the problem up in more two parts.
1) Create sheets with name of values in cells marked:
I already got a macro to fix this from the internet.

Sub AddSheets() 'Select range and make x sheets with the names in range
Dim cell As Excel.Range
Dim wbToAddSheetsTo As Excel.Workbook

Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In Selection
    With wbToAddSheetsTo
        .Sheets.Add after:=.Sheets(.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = cell.Value
        If Err.Number = 1004 Then
          Debug.Print cell.Value & " already used as a sheet name"
        End If
        On Error GoTo 0
    End With
Next cell
End Sub

2) Copy the content (of maybe a template sheet) onto all the sheets:
It need to, in some way, copy all information and formating from a template sheet onto all the sheets.


I don't know if this can be done in one step or it have to be done in two steps.
Looking forward to hear from anyone.
Please ask if anything isn't clear and I need to elaborate

Best regards,
X_Nemo

 

[Wessie]

Hi X_Nemo

Should be quite easy.

Instead of

.Sheets.Add after:=.Sheets(.Sheets.Count)

try

.Sheets("[B][I]Template sheet name[/I][/B]".Copy after:=.Sheets(.Sheets.Count)

Peace
R

 

[X_Nemo]

Thank you Wessie. It worked. And what an easy solution

 

[Wessie]

Thank you Wessie. It worked. And what an easy solution

Easy solutions are the best aren't they?