I have a VBA project which creates a months worth of logs from four templates
based on month and year. This part works well but there are several cells
that need formulas to change as the sheets are created. (cell b33
='Jun-1'!b32)
There are about 14 of these on each sheet. Can anyone think of a way to get
the formulas to change as the loop is creating each sheet (make b33 ='Jun
-1'!b32 in worksheet Jun-2, b33='Jun-2'!b32 in worksheet Jun-3).
Here is half the code, but you should get the idea. The other half only
looks at winter months. Notes included, thanks in advance!

Sub fileomatic()
'
' fileomatic
'
Dim whatmonth As Integer 'variable for number of month
Dim firstdate As String 'date as a string
Dim firstday As Date 'date as a date

whatmonth = InputBox("Enter Month Number (1-12)", "File-O-Matic") 'input of
month number
whatyear = InputBox("Enter Year (example 2008)", "File-O-Matic") 'input of
year

firstdate = whatmonth & "/1/" & whatyear 'creates date as a string
firstday = firstdate 'convert date string to a real
date
Mon = Month(firstday) 'retrieves month number from date
i = 30 'start counter for 31 days month (counts to 1)

Select Case whatmonth 'looking for season according to number
Case 6, 7, 8, 9 'summer months
Do While i -1 'counting 31 loops
If Month(firstday + i) = Mon Then 'checking to see if 31st day is
still within the month
dayofweek = Weekday(firstday + i) 'retrieving day of the week from
date
currentday = firstday + i 'calculating loop date
currentday = Format(currentday, "mmm-d") 'formatting loop day for tab
name

Select Case dayofweek 'choosing correct kind of template
Case 1, 7 'weekend template
Sheets("Summer Weekend").Select 'select weekend template
Sheets("Summer Weekend").Copy Befo=Sheets(1) 'pasting copy
as first
Sheets("Summer Weekend (2)").Select
Sheets("Summer Weekend (2)").Name = currentday 'changing tab
name to loop date
Case 2, 3, 4, 5, 6 'weekday template
Sheets("Summer Weekday").Select
Sheets("Summer Weekday").Copy Befo=Sheets(1)
Sheets("Summer Weekday (2)").Select
Sheets("Summer Weekday (2)").Name = currentday