Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating a Macro Question -
I want to create a Macro, so that when the user selects a button, it will ask
them what month do they want to use. Once they select a Month, the system will then create worksheets with each available work day for that month. For example, if i was to say the month I want is July, the system will create individual worksheets called: July 1st, July 3rd, July 4th....etc, etc. Is this possible? If it can not do work days, I would be happy with worksheets for everyday of the month. Any assistance please? |
#2
|
|||
|
|||
I think that it'd be better to ask for any date in the month they want to use.
It might make it easier when you're close to the end/beginning of the year. (Do they mean Jan of next year or Jan of this year???). Option Explicit Sub testme01() Dim myDate As Variant Dim iCtr As Long Dim myStr As String Dim testwks As Worksheet myDate = InputBox(Prompt:="Enter a date", _ Default:=Format(Date, "mmmm dd, yyyy")) If IsDate(myDate) = False Then MsgBox "Please try later" Exit Sub End If myDate = CDate(myDate) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Select Case Weekday(iCtr) Case Is = vbSunday, vbSaturday 'do nothing Case Else myStr = Format(iCtr, "yyyy_mm_dd_dddd") Set testwks = Nothing On Error Resume Next Set testwks = Worksheets(myStr) On Error GoTo 0 If testwks Is Nothing Then Set testwks = Worksheets.Add testwks.Name = myStr End If End Select Next iCtr End Sub I like to use yyyy_mm_dd. It makes it easier to sort and I don't have to worry about different years. (I did add DDDD to see the name of the day, too.) DKS1 wrote: I want to create a Macro, so that when the user selects a button, it will ask them what month do they want to use. Once they select a Month, the system will then create worksheets with each available work day for that month. For example, if i was to say the month I want is July, the system will create individual worksheets called: July 1st, July 3rd, July 4th....etc, etc. Is this possible? If it can not do work days, I would be happy with worksheets for everyday of the month. Any assistance please? -- Dave Peterson |
#3
|
|||
|
|||
Thanks, that seemed to do the trick. Can I have it create the worksheets
from lowest to highest? "Dave Peterson" wrote: I think that it'd be better to ask for any date in the month they want to use. It might make it easier when you're close to the end/beginning of the year. (Do they mean Jan of next year or Jan of this year???). Option Explicit Sub testme01() Dim myDate As Variant Dim iCtr As Long Dim myStr As String Dim testwks As Worksheet myDate = InputBox(Prompt:="Enter a date", _ Default:=Format(Date, "mmmm dd, yyyy")) If IsDate(myDate) = False Then MsgBox "Please try later" Exit Sub End If myDate = CDate(myDate) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Select Case Weekday(iCtr) Case Is = vbSunday, vbSaturday 'do nothing Case Else myStr = Format(iCtr, "yyyy_mm_dd_dddd") Set testwks = Nothing On Error Resume Next Set testwks = Worksheets(myStr) On Error GoTo 0 If testwks Is Nothing Then Set testwks = Worksheets.Add testwks.Name = myStr End If End Select Next iCtr End Sub I like to use yyyy_mm_dd. It makes it easier to sort and I don't have to worry about different years. (I did add DDDD to see the name of the day, too.) DKS1 wrote: I want to create a Macro, so that when the user selects a button, it will ask them what month do they want to use. Once they select a Month, the system will then create worksheets with each available work day for that month. For example, if i was to say the month I want is July, the system will create individual worksheets called: July 1st, July 3rd, July 4th....etc, etc. Is this possible? If it can not do work days, I would be happy with worksheets for everyday of the month. Any assistance please? -- Dave Peterson |
#4
|
|||
|
|||
Yep:
Option Explicit Sub testme01() Dim myDate As Variant Dim iCtr As Long Dim myStr As String Dim testwks As Worksheet myDate = InputBox(Prompt:="Enter a date", _ Default:=Format(Date, "mmmm dd, yyyy")) If IsDate(myDate) = False Then MsgBox "Please try later" Exit Sub End If myDate = CDate(myDate) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Select Case Weekday(iCtr) Case Is = vbSunday, vbSaturday 'do nothing Case Else myStr = Format(iCtr, "yyyy_mm_dd_dddd") Set testwks = Nothing On Error Resume Next Set testwks = Worksheets(myStr) On Error GoTo 0 If testwks Is Nothing Then Set testwks _ = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) testwks.Name = myStr End If End Select Next iCtr End Sub ---- A couple links for the future--if you ever want to sort worksheets after they've been added: Chip Pearson's: http://www.cpearson.com/excel/sortws.htm David McRitchie's: http://www.mvps.org/dmcritchie/excel...#sortallsheets If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ===== And I like my naming convention even more if you ever have to sort them! DKS1 wrote: Thanks, that seemed to do the trick. Can I have it create the worksheets from lowest to highest? "Dave Peterson" wrote: I think that it'd be better to ask for any date in the month they want to use. It might make it easier when you're close to the end/beginning of the year. (Do they mean Jan of next year or Jan of this year???). Option Explicit Sub testme01() Dim myDate As Variant Dim iCtr As Long Dim myStr As String Dim testwks As Worksheet myDate = InputBox(Prompt:="Enter a date", _ Default:=Format(Date, "mmmm dd, yyyy")) If IsDate(myDate) = False Then MsgBox "Please try later" Exit Sub End If myDate = CDate(myDate) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Select Case Weekday(iCtr) Case Is = vbSunday, vbSaturday 'do nothing Case Else myStr = Format(iCtr, "yyyy_mm_dd_dddd") Set testwks = Nothing On Error Resume Next Set testwks = Worksheets(myStr) On Error GoTo 0 If testwks Is Nothing Then Set testwks = Worksheets.Add testwks.Name = myStr End If End Select Next iCtr End Sub I like to use yyyy_mm_dd. It makes it easier to sort and I don't have to worry about different years. (I did add DDDD to see the name of the day, too.) DKS1 wrote: I want to create a Macro, so that when the user selects a button, it will ask them what month do they want to use. Once they select a Month, the system will then create worksheets with each available work day for that month. For example, if i was to say the month I want is July, the system will create individual worksheets called: July 1st, July 3rd, July 4th....etc, etc. Is this possible? If it can not do work days, I would be happy with worksheets for everyday of the month. Any assistance please? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a Macro Question -
Thanks Dave...just what I was looking for today!!
"Dave Peterson" wrote: Yep: Option Explicit Sub testme01() Dim myDate As Variant Dim iCtr As Long Dim myStr As String Dim testwks As Worksheet myDate = InputBox(Prompt:="Enter a date", _ Default:=Format(Date, "mmmm dd, yyyy")) If IsDate(myDate) = False Then MsgBox "Please try later" Exit Sub End If myDate = CDate(myDate) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Select Case Weekday(iCtr) Case Is = vbSunday, vbSaturday 'do nothing Case Else myStr = Format(iCtr, "yyyy_mm_dd_dddd") Set testwks = Nothing On Error Resume Next Set testwks = Worksheets(myStr) On Error GoTo 0 If testwks Is Nothing Then Set testwks _ = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) testwks.Name = myStr End If End Select Next iCtr End Sub ---- A couple links for the future--if you ever want to sort worksheets after they've been added: Chip Pearson's: http://www.cpearson.com/excel/sortws.htm David McRitchie's: http://www.mvps.org/dmcritchie/excel...#sortallsheets If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ===== And I like my naming convention even more if you ever have to sort them! DKS1 wrote: Thanks, that seemed to do the trick. Can I have it create the worksheets from lowest to highest? "Dave Peterson" wrote: I think that it'd be better to ask for any date in the month they want to use. It might make it easier when you're close to the end/beginning of the year. (Do they mean Jan of next year or Jan of this year???). Option Explicit Sub testme01() Dim myDate As Variant Dim iCtr As Long Dim myStr As String Dim testwks As Worksheet myDate = InputBox(Prompt:="Enter a date", _ Default:=Format(Date, "mmmm dd, yyyy")) If IsDate(myDate) = False Then MsgBox "Please try later" Exit Sub End If myDate = CDate(myDate) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Select Case Weekday(iCtr) Case Is = vbSunday, vbSaturday 'do nothing Case Else myStr = Format(iCtr, "yyyy_mm_dd_dddd") Set testwks = Nothing On Error Resume Next Set testwks = Worksheets(myStr) On Error GoTo 0 If testwks Is Nothing Then Set testwks = Worksheets.Add testwks.Name = myStr End If End Select Next iCtr End Sub I like to use yyyy_mm_dd. It makes it easier to sort and I don't have to worry about different years. (I did add DDDD to see the name of the day, too.) DKS1 wrote: I want to create a Macro, so that when the user selects a button, it will ask them what month do they want to use. Once they select a Month, the system will then create worksheets with each available work day for that month. For example, if i was to say the month I want is July, the system will create individual worksheets called: July 1st, July 3rd, July 4th....etc, etc. Is this possible? If it can not do work days, I would be happy with worksheets for everyday of the month. Any assistance please? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a macro | New Users to Excel | |||
Creating array formulaes in macro | Excel Discussion (Misc queries) | |||
2 part question - macro / command button | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Creating a macro | Excel Worksheet Functions |