A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 20th, 2005, 06:59 PM
clyonesse
external usenet poster
 
Posts: n/a
Default 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 ?
  #2  
Old January 20th, 2005, 07:07 PM
Jimbola
external usenet poster
 
Posts: n/a
Default

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 ?

  #3  
Old January 20th, 2005, 07:57 PM
Ron de Bruin
external usenet poster
 
Posts: n/a
Default

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 ?



  #5  
Old January 21st, 2005, 01:35 AM
Gord Dibben
external usenet poster
 
Posts: n/a
Default

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 ?


  #6  
Old September 18th, 2005, 10:54 PM
PCakes
external usenet poster
 
Posts: n/a
Default

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 ?

  #7  
Old September 20th, 2005, 01:11 AM
Gord Dibben
external usenet poster
 
Posts: n/a
Default

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 ?


  #8  
Old September 20th, 2005, 09:39 PM
PCakes
external usenet poster
 
Posts: n/a
Default

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 ?



  #9  
Old September 20th, 2005, 10:55 PM
Gord Dibben
external usenet poster
 
Posts: n/a
Default

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 ?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
insert cross reference to multiple captions giovanni General Discussion 4 September 13th, 2007 05:21 PM
Print all charts in a workbook (multiple worksheets) aewsaws Charts and Charting 3 December 31st, 2004 11:31 AM
unlocking multiple worksheets Peter Loveridge General Discussion 2 October 7th, 2004 07:24 AM


All times are GMT +1. The time now is 04:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.