HOW DO I DUPLICATE ONE SHEET MULTIPLE TIMES

  • Thread starter Thread starter LC
  • Start date Start date
L

LC

How do i copy data from one sheet to another, to include the footer and
header.
I created a log for work. I need 20 of them to be able to fit everyones name
on it. I would like to have 20 separate sheets.

Am I able to have 20 sheets in excel?

Thank you.
 
Sub makeshts()
For i = 1 To 21
Sheets("Template").Copy After:=Sheets(i)
Next i
End Sub
 
One method is to right click on the Tab of the worksheet you want to copy,
then select Move or Copy. When the Move or Copy box opens, check the "Create
a Copy" box. You now have a new tab (that you should probably rename) and
the worksheet has the same headers, footers, print area and data as the
copied tab. You can easily put 20 tabs in a workbook. Good luck.
 
Easily done with a macro.

Sub CreateNameSheets()
' by Dave Peterson
' List your 20 names required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each mycell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = mycell.Value
If Err.Number <> 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next mycell

End Sub


Gord Dibben MS Excel MVP
 
Back
Top