ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Work book with working days only (https://www.excelbanter.com/excel-programming/396025-work-book-working-days-only.html)

Thyag
Work book with working days only
 
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag

Vergel Adriano
Work book with working days only
 
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub


--
Hope that helps.

Vergel Adriano


"Thyag" wrote:

Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag

Thyag
Work book with working days only
 
On Aug 21, 12:56 pm, Vergel Adriano
wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano



"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?


Thanks,
Thyag

Jana[_3_]
Work book with working days only
 
On Aug 21, 10:04 am, Thyag wrote:
On Aug 21, 12:56 pm, Vergel Adriano





wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long


dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1


dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend


End Sub

You can change the format of the sheet names as needed.

HTH,
Jana

Jana[_3_]
Work book with working days only
 
On Aug 21, 10:31 am, Jana wrote:
On Aug 21, 10:04 am, Thyag wrote:





On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana

Thyag
Work book with working days only
 
On Aug 21, 1:34 pm, Jana wrote:
On Aug 21, 10:31 am, Jana wrote:





On Aug 21, 10:04 am, Thyag wrote:

On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.

Jana[_3_]
Work book with working days only
 
On Aug 21, 10:41 am, Thyag wrote:
On Aug 21, 1:34 pm, Jana wrote:





On Aug 21, 10:31 am, Jana wrote:

On Aug 21, 10:04 am, Thyag wrote:

On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.- Hide quoted text -

- Show quoted text -
Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1


dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana

Thyag
Work book with working days only
 
On Aug 21, 1:56 pm, Jana wrote:
On Aug 21, 10:41 am, Thyag wrote:



On Aug 21, 1:34 pm, Jana wrote:

On Aug 21, 10:31 am, Jana wrote:

On Aug 21, 10:04 am, Thyag wrote:

On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.- Hide quoted text -

- Show quoted text -

Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Hi,
It is really fantastic.
Is it possible to give only month + year and get the same result...?

Thanks,
Thyag

Dale Fye
Work book with working days only
 
lookup the format function in help and figure it out!

--
Email address is not valid.
Please reply to newsgroup only.


"Thyag" wrote:

On Aug 21, 1:56 pm, Jana wrote:
On Aug 21, 10:41 am, Thyag wrote:



On Aug 21, 1:34 pm, Jana wrote:

On Aug 21, 10:31 am, Jana wrote:

On Aug 21, 10:04 am, Thyag wrote:

On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.- Hide quoted text -

- Show quoted text -

Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Hi,
It is really fantastic.
Is it possible to give only month + year and get the same result...?

Thanks,
Thyag

Thyag
Work book with working days only
 
On Aug 21, 2:44 pm, Dale Fye wrote:
lookup the format function in help and figure it out!

--
Email address is not valid.
Please reply to newsgroup only.



"Thyag" wrote:
On Aug 21, 1:56 pm, Jana wrote:
On Aug 21, 10:41 am, Thyag wrote:

On Aug 21, 1:34 pm, Jana wrote:

On Aug 21, 10:31 am, Jana wrote:

On Aug 21, 10:04 am, Thyag wrote:

On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.- Hide quoted text -

- Show quoted text -

Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Hi,
It is really fantastic.
Is it possible to give only month + year and get the same result...?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thanks

Jana[_3_]
Work book with working days only
 
On Aug 21, 11:44 am, Dale Fye wrote:
lookup the format function in help and figure it out!

--
Email address is not valid.
Please reply to newsgroup only.



"Thyag" wrote:
On Aug 21, 1:56 pm, Jana wrote:
On Aug 21, 10:41 am, Thyag wrote:

On Aug 21, 1:34 pm, Jana wrote:

On Aug 21, 10:31 am, Jana wrote:

On Aug 21, 10:04 am, Thyag wrote:

On Aug 21, 12:56 pm, Vergel Adriano

wrote:
here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano

"Thyag" wrote:
Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag- Hide quoted text -

- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.- Hide quoted text -

- Show quoted text -

Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Hi,
It is really fantastic.
Is it possible to give only month + year and get the same result...?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyang:

Although it is possible, sometimes it is easier to deal with a minor
inconvenience (in this case typing 7/1/08 instead of 07/2008) than to
try to program the ideal solution. This solution gives you the
ultimate flexibility for your begin date (what if you want to begin in
the middle of the month for some reason?), while keeping the
likelihood of an invalid entry in the InputBox to a minimum. Also,
for the current year's months, you can just enter July as 7/1 and it
will assume the 2007.

HTH,
Jana


All times are GMT +1. The time now is 04:48 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com