That was a typo.

Yes, it will loop through all the tabs.

Sub FillSheets()
Dim rw as Long
Dim sh as Worksheet
Dim sStr as String
Dim rng as Range
for each sh in ThisWorkbook.Worksheets
rw = Sh.Range("A1").Value
sStr = "A5:CZ" & rw
set rng = Nothing
on Error Resume Next
set rng = sh.Range(sStr)
On Error goto 0
if not rng is nothing then
rng.Filldown
End If
Next
End Sub

I have modified the code to check and see if the string in A1 is a valid
range (it should be a number between 5 and 65536) I also adjusted the value
for the Variable sStr so it starts in row 5 (another typo).

--
Regards,
Tom Ogilvy



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