What is the quickest method to insert & name multiple worksheets .

[Guest]

Need to create several spreadsheets ... each containing multiple worksheets
....

Example: Monthly spreadsheets containing a separate worksheet for each day
of that month ... Jan-1; Jan-2; Jan-3, etc.

Aside from inserting/copying on an individual worksheet basis (too
time-consuming), is there any way to create & name multiple worksheets within
the same spreadsheet ?

 

[Guest]

There are really only 3 ways to do this

1_ Manually-but as you say too time consuming
2_Use John Walkenbac PUP add-in but this cost about £36 ($50)
3_Create a macro.

I have PUP so can do 2 for you if you give e-mail address to send the
workbook to (1 time offer)

 

[Ron de Bruin]

Hi clyonesse

With the list in "Sheet1" A1:A31
jan-1
jan-2
.....
....

You can use this macro to create a workbook with the sheets you want

Sub test()
Dim cell As Range
Dim WSNew As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add(1)
wb2.Sheets(1).Name = wb1.Sheets("Sheet1").Range("A1").Text
For Each cell In wb1.Sheets("Sheet1").Range("A2:A100").SpecialCells(xlCellTypeConstants)
Set WSNew = wb2.Worksheets.Add(after:=Worksheets(wb2.Worksheets.Count))
WSNew.Name = cell.Text
Next cell
End Sub

 

[Guest]

Jimbola -

A VERY generous offer on your part ... if your calendar permits, I would
like to take you up on your offer ... but will understand if this is not
possible. Would only need 2 spreadsheets with daily worksheets in each: One
each for Feb & Mar

Example: Feb-1 / Feb-2 / Feb-3 / and so on ...

My e-mail address is: (e-mail address removed)

Even if you can't provide the spreadsheets, your response is greatly
appreciated !

- clyonesse

 

[Gord Dibben]

Sub AddBook_Sheets()
Workbooks.Add
For i = 31 To 1 Step -1
Worksheets.Add.Name = "Jan-" & i
Next
End Sub

Adjust to suit for each month.


Gord Dibben Excel MVP

 

[Guest]

If I wanted to use this macro, but copy a specific sheet (Template) several
time and have it nameed as Jan-1, Jan-2, etc... How would I accomplish this?

Pcakes

 

[Gord Dibben]

Sub DupSheet()
Dim Counter As Integer
Application.ScreenUpdating = False
Copies = InputBox("How many Copies")
For Counter = 0 To Copies - 1
Sheets("Sheet1").Copy , Worksheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = "Jan - " & Counter + 1
Next
End Sub


Gord Dibben Excel MVP

 

[Guest]

Thank you, works wonderful!

Sub DupSheet()
Dim Counter As Integer
Application.ScreenUpdating = False
Copies = InputBox("How many Copies")
For Counter = 0 To Copies - 1
Sheets("Sheet1").Copy , Worksheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = "Jan - " & Counter + 1
Next
End Sub


Gord Dibben Excel MVP

 

[Gord Dibben]

Thanks for the feedback.

Gord

Thank you, works wonderful!