Tom,
Thanks for your response.

Sorry to bother you but when I went to run this macro, it stopped on the
"Dim rw as Row" line with an error message: "Compile error: User-defined type
not defined". Not really understanding what to do, I tried changing "as Row"
to "as Integer" then "as String". In both cases, it went into the body of
the macro but at the line, "sh.Range(sStr).FillDown" an error message said,
"<Run-time error '1004'. Method 'Range' of object'_Woksheet' failed.
Side question: Is this macro going to loop through all the tabs of this
spreadsheet?
Thanks again for helping me.

"Tom Ogilvy" wrote:

Dim rw as Row
Dim sh as Worksheet
Dim sStr as String
for each sh in ThisWorkbook.Worksheets
rw = Sh.Range("A1").Value
sStr = "A" & rw & ":CZ" & rw
sh.Range(sStr).Filldown
Next

--
Regards,
Tom Ogilvy


"PZ Straube" wrote in message
...
Hello,

I have an Excel 2003 spreadshet with 50+ tabs where in each tab I need to
copy formulas down a certain number of rows across a large number of
columns
Each tab is different in terms of how many rows need to be copied. And,
the
number of rows for each tab changes periodically. I currently do this
manually but I sometimes don't get everything copied properly on the first
shot so I thought I would be better off if I had a macro automate the
process.

Cell A1 contains a value which is equal to the last row number that the
copying needs to go down to. Row 5 is the first row containing the
formulas
that need to be copied. Thus, for columns A:CZ from row 5 to the row
specified in cell A1, I need to "copy down" those formulas

A static address/range macro with the last row of 158 (as specified in
cell
A1) would look like:
*********************
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A5:CZ158").Select
Selection.FillDown
*********************
I think I need to replace the "158" part of <Range("A5:CZ158").Select
with
something dynamic, that changes with the value in cell A1.

I tried:
*********************
Dim Number_of_Rows As Integer
Number_of_Rows = Range("A1").Value

Last_Cell_for_FillDown = "CZ" & A1
Range_For_FillDown = "A5:" & Last_Cell_for_FillDown

Range(Range_For_FillDown).Select
Selection.FillDown
*********************
Frankly, I don't know if I need the DIM or not. The macro stopped at the
RANGE line with a <Run-time error '1004'. Method 'Range' of
object'_Global'
failed. When I hit the HELP button, a Visual Basic Help window popped up
wtih nothing in it.

Any help will be greatly appreciated.