Solved: Copying sheets and incrementing names

[BexleyManor]

I've attached a workbook with as far as I've got!

I would dearly like this code to be able to create a new sheet for every day of the month except weekends. So for the month of May it would skip making sheets for the 6-7, 13-14, 20-21 & 27-28 days.

Can anybody please offer some advice??

Thanks folks!

[lui]

Adding the format$ will help with renaming the sheet
eg. ws.Name = Format$(Ndat, "dd-mm-yy")

[BexleyManor]

Thanks for the input Lui, much appreciated.

I'd actually had a moment of clarity (coffee) and figured that one myself!! Still not solved the weekend stuff??

[mdmackillop]

[vba]Option Explicit
Sub Macro1()
Dim Mth As Long, Chk As Long, i As Long
Application.ScreenUpdating = False
Mth = InputBox("Enter month number")
For i = 1 To 31
On Error GoTo exits
Chk = DateValue(i & "/" & Mth & Format(Now, "/yyyy"))
If Weekday(Chk) <> 1 And Weekday(Chk) <> 7 Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Chk, "ddd dd-mm-yy")
End If
Next
Application.ScreenUpdating = True
exits:
End Sub

[/vba]

[BexleyManor]

Mdm, If you could only see the size of the smile on my collegues face right now. You've made an old man very happy!!

Thank you so much for your excellent suggestion. Truly grateful.

[mdmackillop]

If you need the sheets in reverse order then use
[vba]Option Explicit
Sub Macro2()
Dim Mth As Long, Chk As Long, i As Long
Application.ScreenUpdating = False
Mth = InputBox("Enter month number")
For i = 31 To 1 Step -1
On Error Resume Next
Chk = DateValue(i & "/" & Mth & Format(Now, "/yyyy"))
If Weekday(Chk) <> 1 And Weekday(Chk) <> 7 Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Chk, "ddd dd-mm-yy")
End If
Next
Application.ScreenUpdating = True
End Sub
[/vba]

[BexleyManor]

Ahhh, I was close as I'd started tinkering with the 1 & 31. I'd tried putting 31 to 1 and -31 to 1 but alas, one didn't think of your excellent suggestion!!

Thank you for the great help and advice.

[mdmackillop]

Glad to help.
You also need to change the error statement so as not to exit when 31 is used in a 30 day month.
Regards
MD

[BexleyManor]

You also need to change the error statement

Could you please enlighten me further??

[mdmackillop]

Ahhh, I was close as I'd started tinkering with the 1 & 31. I'd tried putting 31 to 1 and -31 to 1 but alas, one didn't think of your excellent suggestion!!

Thank you for the great help and advice.

My original code was set to exit when a non-existent date (29/30 Feb, 31 Apr etc.) was encountered, ie an error occurred (On Error GoTo Exits). If you are starting from 31 and stepping down to 1, the error will still occur, but has to be handled differently. In this case, ignore the error and test the next value (On Error Resume Next)

You'll find that errors commonly occur as the result of code operation and you need to plan to handle that error to avoid the error message box appearing and to continue the code where appropriate.
Regards
MD

[mdmackillop]

Ahhh, I was close as I'd started tinkering with the 1 & 31. I'd tried putting 31 to 1 and -31 to 1 but alas, one didn't think of your excellent suggestion!!

Thank you for the great help and advice.

My original code was set to exit when a non-existent date (29/30 Feb, 31 Apr etc.) was encountered, ie an error occurred (On Error GoTo Exits). If you are starting from 31 and stepping down to 1, the error will still occur, but has to be handled differently. In this case, ignore the error and test the next value (On Error Resume Next)

You'll find that errors commonly occur as the result of code operation and you need to plan to handle that error to avoid the error message box appearing and to continue the code where appropriate.
Regards
MD

[BexleyManor]

Wow, of course. I'd always avoided the resume next option as I'd thought it always more appropriate to trap and deal with the error!!

Thanks MD, you really provided some excellent advice.