I thought I should share a solution to a small bug in my previous macro. The
code that I thought was complete ended up having a bug. If the Month or Year
happens to be different between Today, Friday and Saturday, meaning the month
or year changes within the week, then the value assignments for Friday,
Saturday, Month and/or Year end up being wrong.

For example, if I run the previous macro on Monday, March 31st, 2008, the
value for the month of the coming Friday and Saturday get assigned 35 and 36,
not 03 and 04, the 3rd and 4th of April. Same with the month and then even
the year if you happen to run this macro in the last week of the year and the
year is different between the day you run it and the year of the coming
Friday and Saturday. I will post my new macro below.

Dim FriMonth, SatMonth, FriYear, SatYear, Fri, Sat, Zero, MyWeekday
Zero = 0

' What day of the week is it today?
MyWeekday = Weekday(Now(), 1)
If MyWeekday = 2 Then GoTo Line1001: ' Today is Monday
If MyWeekday = 3 Then GoTo Line1002: ' Today is Tuesday
If MyWeekday = 4 Then GoTo Line1003: ' Today is Wednesday
If MyWeekday = 5 Then GoTo Line1004: ' Today is Thursday
If MyWeekday = 6 Then GoTo Line1005: ' Today is Friday

Line1001:

' Monday

Fri = Day(Now() + 4) ' Friday's Number
Sat = Day(Now() + 5) ' Saturday's Number
If Sat < 10 Then Sat = Zero & Sat
If Fri < 10 Then Fri = Zero & Fri

'Month

FriMonth = Month(Now() + 4) ' Friday's Month
SatMonth = Month(Now() + 5) ' Saturday's Month
If FriMonth < 10 Then FriMonth = Zero & FriMonth
If SatMonth < 10 Then SatMonth = Zero & SatMonth

' Year
FriYear = Right(Year(Now() + 4), 2) ' Friday's year
SatYear = Right(Year(Now() + 5), 2) ' Saturday's year

GoTo Line1006:

Line1002:

' Tuesday

Fri = Day(Now() + 3) ' Friday's Number
Sat = Day(Now() + 4) ' Saturday's Number
If Sat < 10 Then Sat = Zero & Sat
If Fri < 10 Then Fri = Zero & Fri

'Month

FriMonth = Month(Now() + 3) ' Friday's Month
SatMonth = Month(Now() + 4) ' Saturday's Month
If FriMonth < 10 Then FriMonth = Zero & FriMonth
If SatMonth < 10 Then SatMonth = Zero & SatMonth

' Year
FriYear = Right(Year(Now() + 3), 2) ' Friday's year
SatYear = Right(Year(Now() + 4), 2) ' Saturday's year

GoTo Line1006:

Line1003:

' Wednesday

Fri = Day(Now() + 2) ' Friday's Number
Sat = Day(Now() + 3) ' Saturday's Number
If Sat < 10 Then Sat = Zero & Sat
If Fri < 10 Then Fri = Zero & Fri

'Month

FriMonth = Month(Now() + 2) ' Friday's Month
SatMonth = Month(Now() + 3) ' Saturday's Month
If FriMonth < 10 Then FriMonth = Zero & FriMonth
If SatMonth < 10 Then SatMonth = Zero & SatMonth

' Year
FriYear = Right(Year(Now() + 2), 2) ' Friday's year
SatYear = Right(Year(Now() + 3), 2) ' Saturday's year

GoTo Line1006:

Line1004:

' Thursday

Fri = Day(Now() + 1) ' Friday's Number
Sat = Day(Now() + 2) ' Saturday's Number
If Sat < 10 Then Sat = Zero & Sat
If Fri < 10 Then Fri = Zero & Fri

'Month

FriMonth = Month(Now() + 1) ' Friday's Month
SatMonth = Month(Now() + 2) ' Saturday's Month
If FriMonth < 10 Then FriMonth = Zero & FriMonth
If SatMonth < 10 Then SatMonth = Zero & SatMonth

' Year
FriYear = Right(Year(Now() + 1), 2) ' Friday's year
SatYear = Right(Year(Now() + 2), 2) ' Saturday's year

GoTo Line1006:

Line1005:

' Friday

Fri = Day(Now()) ' Friday's Number
Sat = Day(Now() + 1) ' Saturday's Number
If Sat < 10 Then Sat = Zero & Sat
If Fri < 10 Then Fri = Zero & Fri

'Month

FriMonth = Month(Now()) ' Friday's Month
SatMonth = Month(Now() + 1) ' Saturday's Month
If FriMonth < 10 Then FriMonth = Zero & FriMonth
If SatMonth < 10 Then SatMonth = Zero & SatMonth

' Year
FriYear = Right(Year(Now()), 2) ' Friday's year
SatYear = Right(Year(Now() + 1), 2) ' Saturday's year

Line1006:

' Import this weeks Work Order & Wrap Work Order
ChDir "\\fileserver\" & SatYear & SatMonth & Sat
Workbooks.Open
"\\FileServer\"&SatYear&SatMonth&Sat&"\P"&FriMonth &Fri&FriYear&".XLS")