Insert Worksheets from List in Another Worksheet

[Larry]

I list of names in column A of a Worksheet. I want to create a series of
worksheets in another document using that list of names, all with the same
format as the main sheet. Is there a way to do that?

 

[Dave Peterson]

It's possible, but is that other workbook an existing workbook (what is its
name)--or a new workbook?

Where is that main sheet and what is its name?

 

[Larry]

Basically, I want to create an exact duplicate of a spreadsheet for each
provider, I know how to copy and paste into multiple worksheets at once, so I
guess what I need is a quick way to take the list of 98 names and make a
blank worksheet for each name into a new workbook.

 

[Dave Peterson]

This will create a new worksheet in the same workbook for each name in A1:Axx of
Sheet1:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("Sheet1") '<-- sheet with the names

With wks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
On Error Resume Next
Worksheets.Add.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Couldn't rename: " & ActiveSheet.Name _
& " to " & myCell.Value
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub

 

[Gord Dibben]

Dave

But that won't copy a formatted sheet.

Just adds new default sheets.

Here is a macro you posted a couple or few years ago.......................

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames 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

 

[Dave Peterson]

I tried my best to get the OP to share the name of that template worksheet.


I'm not sure if a new workbook was really needed, either--or if it was supposed
to be a new workbook for each name...

 

[Gord Dibben]

I interpreted "all with the same format as the main sheet" to mean the
sheet formatting.

Although OP did state "blank worksheet" so now not sure<g>


Gord

 

[Dave Peterson]

I'm in that same boat, too.

I interpreted "all with the same format as the main sheet" to mean the
sheet formatting.

Although OP did state "blank worksheet" so now not sure<g>

Gord