changing formulas across worksheets

[Paolo]

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 Before:=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 Before:=Sheets(1)
Sheets("Summer Weekday (2)").Select
Sheets("Summer Weekday (2)").Name = currentday

 

[Gary''s Student]

This assumes that you are depositing the formula in B33 in ActiveSheet:

prev = Split(ActiveSheet.Name, "-")(1) - 1
ActiveSheet.Range("B33").Formula = "='Jun-" & prev & "'!B32"

The first line looks at the name of the activesheet, pulls out the number
part, and subtracts one from it.

The second line just deposits the formula based upon the number from the
first line.

 

[Joel]

This is what I would do

1) Add the sheet name to a cell, let say ther sheet name is put in cell M1.
2) Use in your formulas the function

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

=ADDRESS(2,3,1,TRUE,"[Book1]Sheet1")

from
b33 ='Jun -1'!b32
to
B33 = INDIRECT(ADDRESS(32,2,1,TRUE,M1 & "-" & SubtractNum))

where
M1 = "Jun"
SubtractNum = 1

 

[Paolo]

tried it and get "object required" error at first line. what if sheet is not
active? Can I just put this in the loop I've got going or where should I put
it in this project?

Thanks

 

[Gary''s Student]

The lines can be included in the following context:

1. you have a loop creating worksheets
2. you have already named the latest created worksheet something like Jun-23
3. because Jun-23 have just been created, it is the activesheet.
4. the two lines will deposit in cell B33 in Jun-23 the following formula:

='Jun-22'!B32

 

[Mike Fogleman]

Won't this produce a #REF error because the referenced sheet JUN-22 has not
been created yet? Perhaps he should create the sheets beginning with day 1
so the referenced sheet will be there when the formula is created or loop
through the sheets after they are all created.
Mike F

 

[Gary''s Student]

You are correct.

The sheets must be created in the correct order.

 

[Paolo]

Thanks guys, I am going to try this tonight at work, I am using office 2007
and am worndering if the "split" function will work and maybe that is why I
get the object required error. I am almost there, thanks for all your
help!!!!!!

 

[Paolo]

this worked great once I got the sheet creation order taken care of. One
problem that arose after running program is that the formula is written for
current month but sheet is created by user by inputting month and year, right
now I have the formulas being inserted as 'Jun- " but when another month is
input at start of project Jun- is still input. Can I get the formulas to
change month as input by user?

At start of code I have variants of whatmonth, whatyear, can these be used
somehow or maybe off the name of each sheet which is (currentday)?

Thanks again, the help has been great!