Inserting multiple worksheets

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

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)>EOMONTH($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 before:=Sheets("Template")
ActiveSheet.Name = Format(Sheets("Days").Cells(i, 1), "mmm dd")
Next i
End Sub

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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******

 

[Don Guillett]

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

 

[Guest]

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

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