Macro to Create New Date Tabs in Worksheet

[acctemp]

I have numerous workbooks and every day the latest worksheet has to be
copied and the date changed on the tab (with most recent always on the
left). The procedure is right click on tab, move or copy, create a
copy, right click, rename to prior business day. The dates are not
neccessarily sequential as they follow normal business days for
corporate offices of banks, e.g., excluding weekends and established
holidays. The format for the dates is the month hyphen day with no
leading zeros, like this "9-1" or "10-12". My current worksheet reads
on the tabs as follows: 9-18, 9-15, 9-14, 9-13, 9-12, 9-11, 9-8, 9-7,
9-6, 9-5, 9-1.

If a macro were made to generate a new tab on command would it be
efficient to reference a table of bank dates? I would create a table
of valid dates for the entire year.

Thanks again, I always get great answers here.

 

[Bob Phillips]

No need to hold a table of dates, just use something like

myDate = date -1
If Weekday(myDate) =1 Then
myDate = myDate -2
ElseIf Weekday(myDate) = 7 Then
myDate = myDate -1
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[acctemp]

Bob,

That helps however that does not include bank holidays, only weekends.

Thx

 

[Bob Phillips]

This should cover the holidays, it assumes the holiday dates are ina named
range 'holidays'

myDate = date -1
If Weekday(myDate) =1 Then
myDate = myDate -2
ElseIf Weekday(myDate) = 7 Then
myDate = myDate -1
End If
If Not IsError(Application.Match(myDate, Range("holidays"), 0)) Then
myDate = myDate - 1
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)