PDA

View Full Version : Solved: Copying sheets and incrementing names



BexleyManor
05-24-2006, 03:14 PM
I've attached a workbook with as far as I've got! :banghead:

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?? : pray2:

Thanks folks!

lui
05-24-2006, 04:00 PM
Adding the format$ will help with renaming the sheet
eg. ws.Name = Format$(Ndat, "dd-mm-yy")

BexleyManor
05-24-2006, 04:36 PM
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
05-24-2006, 04:58 PM
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

BexleyManor
05-25-2006, 02:34 AM
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
05-28-2006, 11:20 AM
If you need the sheets in reverse order then use
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

BexleyManor
05-28-2006, 04:45 PM
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
05-28-2006, 10:56 PM
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
06-01-2006, 03:40 PM
You also need to change the error statement

Could you please enlighten me further??

mdmackillop
06-01-2006, 11:56 PM
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
06-01-2006, 11:56 PM
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
06-02-2006, 02:52 PM
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.