Creating multiple worksheets...

K

Kent K

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
 
G

Gord Dibben

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-click>Insert>Module.

Paste the macro into that module.

File>Save then Alt + q to return to Excel.

Tools>Macro>Macros........select SheetCopy22 and "Run"

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


Gord Dibben MS Excel MVP
 
K

Kent K

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!!
 
K

Kent K

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

Gord Dibben

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
 
Top