Inserting multiple worksheets
 

Hi,
I have a monthly workbook which has a separate sheet for each work day. I
need to create more of these monthly workbooks and was wondering if there is
a way to insert more than one sheet at a time. I have a template for the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even better
if you could only choose workdays and not include any weekends - ok ok now I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

You can give this a try...

Sub test()
Call AddMonthSheets(11, 2006) 'Adds Nov 2006
End Sub

Public Sub AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer)
Dim wks As Worksheet
Dim dte As Date
Dim lCounter As Long

Set wks = Sheets("Sheet1") 'Sheet to be copied
wks.Select
For lCounter = 1 To 31
dte = DateSerial(Yr, Mnth, lCounter)
If Month(dte) = Mnth And (Weekday(dte) < 1 And Weekday(dte) < 7)
Then
wks.Copy After:=ActiveSheet
ActiveSheet.Name = Format(DateSerial(Yr, Mnth, lCounter), "Mmm
dd")
End If
Next lCounter

End Sub
--
HTH...

Jim Thomlinson


"Marta" wrote:

Hi,
I have a monthly workbook which has a separate sheet for each work day. I
need to create more of these monthly workbooks and was wondering if there is
a way to insert more than one sheet at a time. I have a template for the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even better
if you could only choose workdays and not include any weekends - ok ok now I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

Name a ws Template
Name a ws Days
on the days ws in a1 enter the date
12/1/

in a3 this formula (CORRECT FOR WORDWRAP)
=IF(A2="","",IF(IF(WEEKDAY(A2)=6,A2+3,A2+1)EOMONT H($A$2,0),"",IF(WEEKDAY(A2)=6,A2+3,A2+1)))

Execute this macro from the DAYS worksheet

Sub makedays()
lr = Application.Count(Columns(1)) + 1
For i = 2 To lr 'To 2 Step -1
Sheets("Template").Copy befo=Sheets("Template")
ActiveSheet.Name = Format(Sheets("Days").Cells(i, 1), "mmm dd")
Next i
End Sub

--
Don Guillett
SalesAid Software

"Marta" wrote in message
...
Hi,
I have a monthly workbook which has a separate sheet for each work day. I
need to create more of these monthly workbooks and was wondering if there
is
a way to insert more than one sheet at a time. I have a template for the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even
better
if you could only choose workdays and not include any weekends - ok ok now
I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

Hi Jim,
I tried it but I get a syntax error and this line is highlighted: Public Sub
AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer). I'm pretty green
when it comes to macros so I have no idea how to fix that.

Marta

"Jim Thomlinson" wrote:

You can give this a try...

Sub test()
Call AddMonthSheets(11, 2006) 'Adds Nov 2006
End Sub

Public Sub AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer)
Dim wks As Worksheet
Dim dte As Date
Dim lCounter As Long

Set wks = Sheets("Sheet1") 'Sheet to be copied
wks.Select
For lCounter = 1 To 31
dte = DateSerial(Yr, Mnth, lCounter)
If Month(dte) = Mnth And (Weekday(dte) < 1 And Weekday(dte) < 7)
Then
wks.Copy After:=ActiveSheet
ActiveSheet.Name = Format(DateSerial(Yr, Mnth, lCounter), "Mmm
dd")
End If
Next lCounter

End Sub
--
HTH...

Jim Thomlinson


"Marta" wrote:

Hi,
I have a monthly workbook which has a separate sheet for each work day. I
need to create more of these monthly workbooks and was wondering if there is
a way to insert more than one sheet at a time. I have a template for the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even better
if you could only choose workdays and not include any weekends - ok ok now I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

Hi Don,
I tried this as well but I get an error message when I try to run the macro.
Only one sheets ends up getting inserted and it's called Template (2). I
have no idea how to fix this.

Marta

"Don Guillett" wrote:

Name a ws Template
Name a ws Days
on the days ws in a1 enter the date
12/1/

in a3 this formula (CORRECT FOR WORDWRAP)
=IF(A2="","",IF(IF(WEEKDAY(A2)=6,A2+3,A2+1)EOMONT H($A$2,0),"",IF(WEEKDAY(A2)=6,A2+3,A2+1)))

Execute this macro from the DAYS worksheet

Sub makedays()
lr = Application.Count(Columns(1)) + 1
For i = 2 To lr 'To 2 Step -1
Sheets("Template").Copy befo=Sheets("Template")
ActiveSheet.Name = Format(Sheets("Days").Cells(i, 1), "mmm dd")
Next i
End Sub

--
Don Guillett
SalesAid Software

"Marta" wrote in message
...
Hi,
I have a monthly workbook which has a separate sheet for each work day. I
need to create more of these monthly workbooks and was wondering if there
is
a way to insert more than one sheet at a time. I have a template for the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even
better
if you could only choose workdays and not include any weekends - ok ok now
I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

As a guess you are missing the final line of code -- End Sub -- Make sure you
copy all of this (From Here To Here)...

'From Here *****
Sub test()
Call AddMonthSheets(11, 2006)
End Sub

Public Sub AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer)
Dim wks As Worksheet
Dim dte As Date
Dim lCounter As Long

Set wks = Sheets("Sheet1") 'Sheet to be copied
wks.Select
For lCounter = 1 To 31
dte = DateSerial(Yr, Mnth, lCounter)
If Month(dte) = Mnth And (Weekday(dte) < 1 And Weekday(dte) < 7)
Then
wks.Copy After:=ActiveSheet
ActiveSheet.Name = Format(DateSerial(Yr, Mnth, lCounter), "Mmm
dd")
End If
Next lCounter
End Sub
'To Here******
--
HTH...

Jim Thomlinson


"Marta" wrote:

Hi Jim,
I tried it but I get a syntax error and this line is highlighted: Public Sub
AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer). I'm pretty green
when it comes to macros so I have no idea how to fix that.

Marta

"Jim Thomlinson" wrote:

You can give this a try...

Sub test()
Call AddMonthSheets(11, 2006) 'Adds Nov 2006
End Sub

Public Sub AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer)
Dim wks As Worksheet
Dim dte As Date
Dim lCounter As Long

Set wks = Sheets("Sheet1") 'Sheet to be copied
wks.Select
For lCounter = 1 To 31
dte = DateSerial(Yr, Mnth, lCounter)
If Month(dte) = Mnth And (Weekday(dte) < 1 And Weekday(dte) < 7)
Then
wks.Copy After:=ActiveSheet
ActiveSheet.Name = Format(DateSerial(Yr, Mnth, lCounter), "Mmm
dd")
End If
Next lCounter

End Sub
--
HTH...

Jim Thomlinson


"Marta" wrote:

Hi,
I have a monthly workbook which has a separate sheet for each work day. I
need to create more of these monthly workbooks and was wondering if there is
a way to insert more than one sheet at a time. I have a template for the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even better
if you could only choose workdays and not include any weekends - ok ok now I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

I tested Jim's and it is good. Send me your email address and I'll send you
my test workbook.

--
Don Guillett
SalesAid Software

"Marta" wrote in message
...
Hi Don,
I tried this as well but I get an error message when I try to run the
macro.
Only one sheets ends up getting inserted and it's called Template (2). I
have no idea how to fix this.

Marta

"Don Guillett" wrote:

Name a ws Template
Name a ws Days
on the days ws in a1 enter the date
12/1/

in a3 this formula (CORRECT FOR WORDWRAP)
=IF(A2="","",IF(IF(WEEKDAY(A2)=6,A2+3,A2+1)EOMONT H($A$2,0),"",IF(WEEKDAY(A2)=6,A2+3,A2+1)))

Execute this macro from the DAYS worksheet

Sub makedays()
lr = Application.Count(Columns(1)) + 1
For i = 2 To lr 'To 2 Step -1
Sheets("Template").Copy befo=Sheets("Template")
ActiveSheet.Name = Format(Sheets("Days").Cells(i, 1), "mmm dd")
Next i
End Sub

--
Don Guillett
SalesAid Software

"Marta" wrote in message
...
Hi,
I have a monthly workbook which has a separate sheet for each work day.
I
need to create more of these monthly workbooks and was wondering if
there
is
a way to insert more than one sheet at a time. I have a template for
the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way
to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even
better
if you could only choose workdays and not include any weekends - ok ok
now
I
know I'm dreaming :-)

Thanks,
Marta

Inserting multiple worksheets
 

I got it to work and it's great! Thanks guys!!!!

"Don Guillett" wrote:

I tested Jim's and it is good. Send me your email address and I'll send you
my test workbook.

--
Don Guillett
SalesAid Software

"Marta" wrote in message
...
Hi Don,
I tried this as well but I get an error message when I try to run the
macro.
Only one sheets ends up getting inserted and it's called Template (2). I
have no idea how to fix this.

Marta

"Don Guillett" wrote:

Name a ws Template
Name a ws Days
on the days ws in a1 enter the date
12/1/

in a3 this formula (CORRECT FOR WORDWRAP)
=IF(A2="","",IF(IF(WEEKDAY(A2)=6,A2+3,A2+1)EOMONT H($A$2,0),"",IF(WEEKDAY(A2)=6,A2+3,A2+1)))

Execute this macro from the DAYS worksheet

Sub makedays()
lr = Application.Count(Columns(1)) + 1
For i = 2 To lr 'To 2 Step -1
Sheets("Template").Copy befo=Sheets("Template")
ActiveSheet.Name = Format(Sheets("Days").Cells(i, 1), "mmm dd")
Next i
End Sub

--
Don Guillett
SalesAid Software

"Marta" wrote in message
...
Hi,
I have a monthly workbook which has a separate sheet for each work day.
I
need to create more of these monthly workbooks and was wondering if
there
is
a way to insert more than one sheet at a time. I have a template for
the
worksheet but not for the workbook and last time I insterted each sheet
individually which was a pain. Also, I was wondering if there is a way
to
rename all the sheets at once ie: Dec. 1 to Dec. 31, it would be even
better
if you could only choose workdays and not include any weekends - ok ok
now
I
know I'm dreaming :-)

Thanks,
Marta