rw = Sh.Range("A1").Value

Your the one that said the number of rows was in A1 of the sheet being
processed. That is where the code looks for the number of rows. If it
isn't there, not much I can say.

to skip sheets add an if statement in the loop.

for each sh in ThisWorkbook.Worksheets
if ucase(sh.name) < "ABC" and ucase(sh.Name) < "EFG" then





end if
Next

--
Regards,
Tom Ogilvy






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

Thanks for all your time in responding to my query.

Unfortunately, using a temporary MsgBox just before the NEXT line, I found
that "rw" never changes from zero. That means that the Range/Rng is in
error
which further means that it uses the "Then" part of the "If Not rng Is
Nothing..." line and skips over the FillDown line.

Sorry to ask but any suggestions for me?

As a side question regarding looping through all the tabs, since I have
two
supporting tabs for notes in this spreadsheet that should not be affected
by
this macro, is there a way to stop this macro before it loops around and
destroyed those two notes tabs?

Thanks!

"Tom Ogilvy" wrote:

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.