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

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

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

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

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" wrote in message
...
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 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

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" wrote in message
...
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 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

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" wrote in message
...
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 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

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