Copy worksheet with VBA for Each day of month

[David T]

Hello-

I have a worksheet that i need to copy for each day of the month and each
worksheet needs to be named for the corresponding day, however each saturday,
sunday, and monday needs to be on one tab.

For example, i have a tab for day 1 for March name "1" which i want to copy
for each day. Day 2 will be named "2" and 3 be named "3". On day 8 - 10
(which is Saturday - Monday), i want only only 1 tab named "8-10".

Any help is appreciated. thanks.

 

[Bob Phillips]

What happens if the 1st is a sat Sun or Mon?

Public Sub Test()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook

For i = 2 To 31

If Month(DateSerial(Year(Date), Month(Date), i)) = _
Month(DateSerial(Year(Date), Month(Date), i - 1)) Then

Worksheets("1").Copy after:=.Worksheets(.Worksheets.Count)
Set sh = ActiveSheet
If Weekday(DateSerial(Year(Date), Month(Date), i), 2) < 6
Then

sh.Name = i
Else

If Month(DateSerial(Year(Date), Month(Date), i + 2)) = _
Month(DateSerial(Year(Date), Month(Date), i)) Then

sh.Name = i & "-" & i + 2
i = i + 2

ElseIf Month(DateSerial(Year(Date), Month(Date), i + 1))
= _
Month(DateSerial(Year(Date), Month(Date), i)) Then

sh.Name = i & "-" & i + 1
i = i + 1
Else
sh.Name = i
End If
End If
End If
Next i
End With
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

How do you name the sheet when the first of the month falls on a Sunday or
Monday?

How do you name the sheet when the last day of the month falls on Saturday or
Sunday?