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

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 ?

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)


"clyonesse" wrote:

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 ?

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.Worksheet s.Count))
WSNew.Name = cell.Text
Next cell
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"clyonesse" wrote in message ...
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 ?

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:

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

- clyonesse

"Jimbola" wrote:

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)


"clyonesse" wrote:

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 ?

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

On Thu, 20 Jan 2005 09:59:03 -0800, clyonesse
wrote:

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 ?

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

"clyonesse" wrote:

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 ?

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

On Sun, 18 Sep 2005 14:54:02 -0700, PCakes
wrote:

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

"clyonesse" wrote:

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 ?

Thank you, works wonderful!

"Gord Dibben" wrote:

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

On Sun, 18 Sep 2005 14:54:02 -0700, PCakes
wrote:

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

"clyonesse" wrote:

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 ?

Thanks for the feedback.

Gord

On Tue, 20 Sep 2005 13:39:06 -0700, PCakes
wrote:

Thank you, works wonderful!

"Gord Dibben" wrote:

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

On Sun, 18 Sep 2005 14:54:02 -0700, PCakes
wrote:

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

"clyonesse" wrote:

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 ?