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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |