Insert Worksheets from List in Another Worksheet
 

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?

Insert Worksheets from List in Another Worksheet
 

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 wrote:

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

Insert Worksheets from List in Another Worksheet
 

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" wrote:

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 wrote:

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

Insert Worksheets from List in Another Worksheet
 

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

Larry wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

Insert Worksheets from List in Another Worksheet
 

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

On Wed, 02 Sep 2009 12:04:56 -0500, Dave Peterson
wrote:

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

Larry wrote:

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" wrote:

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 wrote:

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

Insert Worksheets from List in Another Worksheet
 

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

But he wouldn't <bg. In fact, he wrote:
...make a
blank worksheet for each name into a new workbook.

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 wrote:

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

On Wed, 02 Sep 2009 12:04:56 -0500, Dave Peterson
wrote:

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

Larry wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

Insert Worksheets from List in Another Worksheet
 

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


On Wed, 02 Sep 2009 15:02:43 -0500, Dave Peterson
wrote:

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

But he wouldn't <bg. In fact, he wrote:
...make a
blank worksheet for each name into a new workbook.

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 wrote:

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

On Wed, 02 Sep 2009 12:04:56 -0500, Dave Peterson
wrote:

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

Larry wrote:

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" wrote:

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 wrote:

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

Insert Worksheets from List in Another Worksheet
 

I'm in that same boat, too.

Gord Dibben wrote:

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

On Wed, 02 Sep 2009 15:02:43 -0500, Dave Peterson
wrote:

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

But he wouldn't <bg. In fact, he wrote:
...make a
blank worksheet for each name into a new workbook.

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 wrote:

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

On Wed, 02 Sep 2009 12:04:56 -0500, Dave Peterson
wrote:

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

Larry wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson