Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
Line chart from multiple worksheets | Charts and Charting in Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Update multiple worksheets | Excel Discussion (Misc queries) | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |