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?

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

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

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

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