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.