Does changing your last line of posted code to this do what you want...

Worksheets("Sheet2").Range("3:" & lStop + 1 - (lStop = 1)).FillDown

--
Rick (MVP - Excel)


"LostInNY" wrote in message
...
I have 2 spreadsheets (Sheet 1 and Sheet 2). Sheet 1 has one header row
and
sheet 2 has 2 header rows. I am using the following to count the number
of
rows in Sheet 1 and copy a formula to every row in Sheet 2 based on the
count
from Sheet 1.


With Worksheets("Sheet1")
lStop = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Worksheets("Sheet2").Range("3:" & lStop + 1).FillDown

The formula is perfect except when I only have one entry in Sheet 1. In
this case the formula copies the second header row in Sheet 2 instead of
the
formula in the 3rd row. Any ideas/suggestions?