Rename Worksheets excluding Weekends and Holidays

[Steve]

Hi All,

I would greatly appreciate any help with the following:

I have an excel workbook that contains a number of worksheets.
The first is called "Control", the next is called "A" and the last sheet
is called "B". In between sheets A and B are individual sheets, one
for every day of a month. In cell A:2 on the control sheet I enter
a start date and the following code renames all the individual
sheets based on the start date e.g. Nov 1, Nov 2 etc.

Sub RenameSheets()
Dim i As Long
Dim sh As Worksheet
Dim dte As Date
Dim dte1 As Date

If IsDate(Worksheets("Control").Range("A2").Value) Then
dte1 = CDate(Worksheets("Control").Range("A2").Value)
dte = dte1
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Control" And sh.Name <> "A" And _
sh.Name <> "B" Then
If Month(dte) = Month(dte1) Then
sh.Name = Format(dte, "mmm d")
sh.Visible = xlSheetVisible
dte = dte + 1
Else
sh.Visible = xlSheetHidden
End If
End If
Next sh
Else
MsgBox "Start position invalid"
End If
End Sub

My issue is that I somehow need the code to ignore weekends and holidays
when it renames the sheets. For example, Nov 5 and Nov 6 were Sunday
and Saturday respectively. I would need the code to jump from Nov 4
to Nov 7 etc to account for the weekend.

Any thoughts and suggestions on how to accomplish the above?

Thanks,

Steve

 

[Peter Rooney]

Steve,

Couldn't you put a condition in your code that uses the weekday function to
say something like:

if weekday(dte) = 1 (Sunday) or Weekday(dte) = 7 (Saturday) then...

Check out the VBA help on WEEKDAY, because you can change the day the Excel
used for 1.

Hope this helps

Pete