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

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.
--
Gary''s Student - gsnu200791


"Paolo" wrote:

> 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

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" wrote:

> 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

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" wrote:

> 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.
> --
> Gary''s Student - gsnu200791
>
>
> "Paolo" wrote:
>
> > 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

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
--
Gary''s Student - gsnu200791


"Paolo" wrote:

> 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" wrote:
>
> > 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.
> > --
> > Gary''s Student - gsnu200791
> >
> >
> > "Paolo" wrote:
> >
> > > 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

You are correct.

The sheets must be created in the correct order.

--
Gary''s Student - gsnu200791


"Mike Fogleman" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:E75F6AA2-32D7-45C2-8A9B-(E-Mail Removed)...
> > 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
> > --
> > Gary''s Student - gsnu200791
> >
> >
> > "Paolo" wrote:
> >
> >> 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" wrote:
> >>
> >> > 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.
> >> > --
> >> > Gary''s Student - gsnu200791
> >> >
> >> >
> >> > "Paolo" wrote:
> >> >
> >> > > 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
>
>
>

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!!!!!!

"Mike Fogleman" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:E75F6AA2-32D7-45C2-8A9B-(E-Mail Removed)...
> > 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
> > --
> > Gary''s Student - gsnu200791
> >
> >
> > "Paolo" wrote:
> >
> >> 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" wrote:
> >>
> >> > 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.
> >> > --
> >> > Gary''s Student - gsnu200791
> >> >
> >> >
> >> > "Paolo" wrote:
> >> >
> >> > > 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
>
>
>

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!

"Gary''s Student" wrote:

> You are correct.
>
> The sheets must be created in the correct order.
>
> --
> Gary''s Student - gsnu200791
>
>
> "Mike Fogleman" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:E75F6AA2-32D7-45C2-8A9B-(E-Mail Removed)...
> > > 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
> > > --
> > > Gary''s Student - gsnu200791
> > >
> > >
> > > "Paolo" wrote:
> > >
> > >> 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" wrote:
> > >>
> > >> > 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.
> > >> > --
> > >> > Gary''s Student - gsnu200791
> > >> >
> > >> >
> > >> > "Paolo" wrote:
> > >> >
> > >> > > 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
> >
> >
> >