I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007

Kent

Stick this macro into a general module in your workbook.

Select the sheet to copy and run the macro.

Sub SheetCopy22()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For i = 1 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "NewSheet" & i
End With
Next i
Application.ScreenUpdating = True
endit:
End Sub

To do the above "sticking" hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the macro into that module.

FileSave then Alt + q to return to Excel.

ToolsMacroMacros........select SheetCopy22 and "Run"

If you want names other than NewSheet1, 2 ,3 etc. post back for more help.


Gord Dibben MS Excel MVP

On Fri, 18 May 2007 19:13:00 -0700, Kent K
wrote:

I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007

Sounds good. Will be my first time using a "macro." I intend on creating
TOC containing all 50 US states. I will be hyperlinking a given cell on TOC
to it's respective worksheet within the book. Each subsequent worksheet will
be Named accordingly.
ie. worksheet 2 = Alabama, ws3 = Alaska, etc.. I will be hyperlinking a
given cell on TOC to it's respective worksheet within the workbook.

Is there a macro which will allow me to name the consecutive worksheets all
at once?

Also, I'm trying to figure out if I can select the same cell in all sub
worksheets and "mass" hyperlink to the TOC. The first two worksheets are
named State Directory (SD) and Regional Directory (RD) respectively. From
ws3 onward, all page layouts are identical, allowing two cells, (A3 & A4) to
be used for hyperlinking to the two directories. I would like to be able to
type SD into A3, and RD into A4 and apply it to all worksheets as well as
being able to create a hyperlink to the directories intended. Is this
possible? Thanks for the help!!

"Gord Dibben" wrote:

Kent

Stick this macro into a general module in your workbook.

Select the sheet to copy and run the macro.

Sub SheetCopy22()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For i = 1 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "NewSheet" & i
End With
Next i
Application.ScreenUpdating = True
endit:
End Sub

To do the above "sticking" hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the macro into that module.

FileSave then Alt + q to return to Excel.

ToolsMacroMacros........select SheetCopy22 and "Run"

If you want names other than NewSheet1, 2 ,3 etc. post back for more help.


Gord Dibben MS Excel MVP

On Fri, 18 May 2007 19:13:00 -0700, Kent K
wrote:

I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007

sorry... one more question. Where do I start and end my "copy" of the macro
you provided?

"Gord Dibben" wrote:

Kent

Stick this macro into a general module in your workbook.

Select the sheet to copy and run the macro.

Sub SheetCopy22()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For i = 1 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "NewSheet" & i
End With
Next i
Application.ScreenUpdating = True
endit:
End Sub

To do the above "sticking" hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the macro into that module.

FileSave then Alt + q to return to Excel.

ToolsMacroMacros........select SheetCopy22 and "Run"

If you want names other than NewSheet1, 2 ,3 etc. post back for more help.


Gord Dibben MS Excel MVP

On Fri, 18 May 2007 19:13:00 -0700, Kent K
wrote:

I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007

Kent

This macro by Dave Peterson will copy a sheet named Template and name each sheet
according to a list of names you have on a List sheet.

It would replace the macro I gave you yesterday.

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

Copy all from Sub CreateNamedSheets() down to End Sub

I will address the TOC in a follow-up post.

Gord

On Fri, 18 May 2007 22:27:00 -0700, Kent K
wrote:

Sounds good. Will be my first time using a "macro." I intend on creating
TOC containing all 50 US states. I will be hyperlinking a given cell on TOC
to it's respective worksheet within the book. Each subsequent worksheet will
be Named accordingly.
ie. worksheet 2 = Alabama, ws3 = Alaska, etc.. I will be hyperlinking a
given cell on TOC to it's respective worksheet within the workbook.

Is there a macro which will allow me to name the consecutive worksheets all
at once?

Also, I'm trying to figure out if I can select the same cell in all sub
worksheets and "mass" hyperlink to the TOC. The first two worksheets are
named State Directory (SD) and Regional Directory (RD) respectively. From
ws3 onward, all page layouts are identical, allowing two cells, (A3 & A4) to
be used for hyperlinking to the two directories. I would like to be able to
type SD into A3, and RD into A4 and apply it to all worksheets as well as
being able to create a hyperlink to the directories intended. Is this
possible? Thanks for the help!!

"Gord Dibben" wrote:

Kent

Stick this macro into a general module in your workbook.

Select the sheet to copy and run the macro.

Sub SheetCopy22()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For i = 1 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "NewSheet" & i
End With
Next i
Application.ScreenUpdating = True
endit:
End Sub

To do the above "sticking" hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the macro into that module.

FileSave then Alt + q to return to Excel.

ToolsMacroMacros........select SheetCopy22 and "Run"

If you want names other than NewSheet1, 2 ,3 etc. post back for more help.


Gord Dibben MS Excel MVP

On Fri, 18 May 2007 19:13:00 -0700, Kent K
wrote:

I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007

See other post.

Gord

On Fri, 18 May 2007 22:39:00 -0700, Kent K
wrote:

sorry... one more question. Where do I start and end my "copy" of the macro
you provided?

"Gord Dibben" wrote:

Kent

Stick this macro into a general module in your workbook.

Select the sheet to copy and run the macro.

Sub SheetCopy22()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For i = 1 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "NewSheet" & i
End With
Next i
Application.ScreenUpdating = True
endit:
End Sub

To do the above "sticking" hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the macro into that module.

FileSave then Alt + q to return to Excel.

ToolsMacroMacros........select SheetCopy22 and "Run"

If you want names other than NewSheet1, 2 ,3 etc. post back for more help.


Gord Dibben MS Excel MVP

On Fri, 18 May 2007 19:13:00 -0700, Kent K
wrote:

I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007

Create the hyperlinks in A3 and A4 on the original sheet before you copy it 54
times.

InsertHyperlinkPlace in this Document.

Additional stuff...................................

You could whip over to David McRitchie's site to see his creating a TOC pages.

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Or see Debra Dalgleish's site for similar using a Toolbar.

http://www.contextures.com/xlToolbar01.html


Gord

On Fri, 18 May 2007 22:27:00 -0700, Kent K
wrote:

Also, I'm trying to figure out if I can select the same cell in all sub
worksheets and "mass" hyperlink to the TOC. The first two worksheets are
named State Directory (SD) and Regional Directory (RD) respectively. From
ws3 onward, all page layouts are identical, allowing two cells, (A3 & A4) to
be used for hyperlinking to the two directories. I would like to be able to
type SD into A3, and RD into A4 and apply it to all worksheets as well as
being able to create a hyperlink to the directories intended. Is this
possible? Thanks for the help!!

Thanks for all the suggestions and links. I really appreciate it.

-K

"Gord Dibben" wrote:

See other post.

Gord

On Fri, 18 May 2007 22:39:00 -0700, Kent K
wrote:

sorry... one more question. Where do I start and end my "copy" of the macro
you provided?

"Gord Dibben" wrote:

Kent

Stick this macro into a general module in your workbook.

Select the sheet to copy and run the macro.

Sub SheetCopy22()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = InputBox("How many times?")
For i = 1 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "NewSheet" & i
End With
Next i
Application.ScreenUpdating = True
endit:
End Sub

To do the above "sticking" hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the macro into that module.

FileSave then Alt + q to return to Excel.

ToolsMacroMacros........select SheetCopy22 and "Run"

If you want names other than NewSheet1, 2 ,3 etc. post back for more help.


Gord Dibben MS Excel MVP

On Fri, 18 May 2007 19:13:00 -0700, Kent K
wrote:

I'm trying to figure out how to duplicate worksheet #1 and then create 54
additional worksheets without having to (copy #1 - create new worksheet -
Paste #1 onto new worksheet - repeat for each additional worksheet) Is this
possible? Using Excel 2007