Worksheet Copier

[Legacy 436357]

Hello,

Does anyone know code that will copy a template worksheet multiple times?

Original Sheet Name: Week Template

I plan to have one worksheet for each week in the year (52 plus the 'Week Template' sheet

Paste conditions:

1) row height
2) column width
3) fonts style sizes and colors
4) borders and border colors
5) cell fills
6) formulas

*basically all forms, fits, and functions of original sheet

Requests:

1) select the worksheet name to copy (Week Template)
2) select the number of sheets to be copied (52)
3) name the worksheets Week1, Week 2, Week 3, etc.

Thank you so very much for your assistance

 

[Fluff]

How about

Sub xjohnson()
   Dim i As Long
   
   For i = 1 To 52
      Sheets("Week template").Copy , Sheets(i)
      ActiveSheet.Name = "Week " & i
   Next i
End Sub

 

[Legacy 436357]

Thanks I tried the code and it stopped at 7 sheets. Any ideas?

 

[shg]

Long-standing bug; see https://support.microsoft.com/en-us...matically-causes-run-time-error-1004-in-excel

Create a single-sheet workbook and save, then.

  For i = 1 To 52
    Worksheets.Add After:=Sheets(Sheets.Count), Type:="C:\myPath\myBook.xlsx"
    ActiveSheet.Name = "Week " & i
  Next i

 

[Legacy 436357]

Your code works there was something wrong with my workbook. I created a new workbook and no issues.

Thank you very much Fluff

 

[Fluff]

You're welcome & thanks for the feedback