I am a serious newby with macros, but would like to modify what I am
doing to avoid having to edit a series of macros I use, everytime I use
them. Essentially, I have a bunch of different workbooks that are more
or less templated copies of each other but vary according to certain
details that don't affect the macro. I have recorded a simple macro
that starts in a cell on the active sheet and then needs to refer to
the next sheet in the book to run a simply formula and pull the
information back to the active sheet. My problem is that everytime I
update the workbook I need to copy the active sheet and apply a new
name to the newly copied sheet. Names of sheets are simply the date
corresponding to the information on that sheet (ie. 03 10 30). My macro
keeps referring back to the sheet I used when I originally recorded it
(obviously!). I would like to edit the code so that the macro knows to
simly go the sheet immediately behind the active sheet. This is a
sample of my code:

Sub start_ct()
'
' start_ct Macro
' Macro recorded 10/13/2003 by Family
'

'
Range("G38:M46").Select
Selection.ClearContents
Range("F38").Select
ActiveCell.FormulaR1C1 = _
"='03 09 17'!RC-'03 09 17'!RC[2]-'03 09 17'!RC[3]+'03 09
17'!RC[4]+'03 09 17'!RC[7]"
Selection.Copy
Range("F39:F46").Select
ActiveSheet.Paste
End Sub


ANy ideas?

Thanks, Lori!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

We'll create a variable to hold th e"source" sheet's
name...

DIM sSheet as String

suppose the sheet you want is yesterday's date...

sSheet = format$(Date-1,"yy mm dd")

' make some adjustment for weekends holidays
' or simply read the date from the active sheet...say its
in cell A1...

sSheet = Format$(Range("A1").Value,"yy mm dd")

Now we can use this in our formula...

Sub start_ct()
Dim sSheet As String
sSheet = Format$(Range("A1").Value, "'yy mm dd'")
With Range("G38:M46")
.ClearContents
.FormulaR1C1 = "=" & _
sSheet & "!RC-" & sSheet & "!RC[2]-" & _
sSheet & "!RC[3]+" & sSheet & "!RC[4]+" & _
sSheet & "!RC[7]"
End With
End Sub


Note that I've also tidied the code a bit - works fine on
my test book ...

HTH
Patrick Molloy
Microsoft Excel MVP

PS for my workbook, email me directly

-----Original Message-----

I am a serious newby with macros, but would like to
modify what I am
doing to avoid having to edit a series of macros I use,
everytime I use
them. Essentially, I have a bunch of different
workbooks that are more
or less templated copies of each other but vary
according to certain
details that don't affect the macro. I have recorded a
simple macro
that starts in a cell on the active sheet and then needs
to refer to
the next sheet in the book to run a simply formula and
pull the
information back to the active sheet. My problem is that
everytime I
update the workbook I need to copy the active sheet and
apply a new
name to the newly copied sheet. Names of sheets are
simply the date
corresponding to the information on that sheet (ie. 03
10 30). My macro
keeps referring back to the sheet I used when I
originally recorded it
(obviously!). I would like to edit the code so that the
macro knows to
simly go the sheet immediately behind the active sheet.
This is a
sample of my code:

Sub start_ct()
'
' start_ct Macro
' Macro recorded 10/13/2003 by Family
'

'
Range("G38:M46").Select
Selection.ClearContents
Range("F38").Select
ActiveCell.FormulaR1C1 = _
"='03 09 17'!RC-'03 09 17'!RC[2]-'03 09 17'!RC[3]+'03 09
17'!RC[4]+'03 09 17'!RC[7]"
Selection.Copy
Range("F39:F46").Select
ActiveSheet.Paste
End Sub


ANy ideas?

Thanks, Lori!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.