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.Worksheets.Count))
WSNew.Name = cell.Text
Next cell
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"clyonesse" <[email protected]> wrote in message news:[email protected]...
> 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: [email protected]
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
<[email protected]> 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 <[email protected]>
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 <[email protected]>
> 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 <[email protected]>
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 <[email protected]>
>> 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 ?
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks