Copy Template tab and rename days of the month - multiple loop

[Wizerd]

All,

I found the code below in the forums and it works great for copying a 'Template' tab for the days of an inputted month - creating a tab for each day. Works like a charm. I would like to create two tabs for each day. I stripped the month off the name and only use the day number (01,02,03) for my tab names, but would like to have 2 for each day (ie: 01A, 01B, 02A, 02B) or (01-Day, 01Aft, 02Day, 02Aft) or soem variant of that. I have the same report go out twice a day for a 2 shift operation, and would like to create a summary page for the month with consistent tab name syntax. Any help would be greatly appreciated.

Sub CreateSheets()
Dim strDate As String
Dim NumDays As Long
Dim i As Long
Dim sh As Object
Dim wsBase As Worksheet
On Error GoTo EndIt

' The Do statement to captures Month/Year via Input Box
' and return number of days in the month to the NumDays variable
Do
strDate = Application.InputBox( _
Prompt:="Please enter month and year: mm/yyyy", _
Title:="Month and Year", _
Default:=Format(Date, "mm/yyyy"), _
Type:=2)

If strDate = "False" Then Exit Sub
If IsDate(strDate) Then Exit Do
If MsgBox("Please enter a valid date, such as ""01/2010""." _
& vbLf & vbLf & "Shall we try again?", vbYesNo + vbExclamation, _
"Invalid Date") = vbNo Then End
Loop

Application.ScreenUpdating = False
NumDays = Day(DateSerial(Year(strDate), Month(strDate) + 1, 0))
Set wsBase = Sheets("Template")

' For each day, the For statement below copies the template sheet 'n' times
For i = 1 To NumDays
wsBase.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(DateSerial(Year(strDate), Month(strDate), i), "ddd mmm dd")
Next i
EndIt:
Application.ScreenUpdating = True
End Sub

 

[John_w]

Try changing the loop to:

For i = 1 To NumDays
  wsBase.Copy After:=Sheets(Sheets.Count)
  ActiveSheet.Name = Format(DateSerial(Year(strDate), Month(strDate), i), "dd") & "A"
  wsBase.Copy After:=Sheets(Sheets.Count)
  ActiveSheet.Name = Format(DateSerial(Year(strDate), Month(strDate), i), "dd") & "B"
Next i

 

[Wizerd]

Worked like a charm. Thanks for the quick insight. Cheers!