Fill Down to Last Value
 

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?

Fill Down to Last Value
 

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?

Fill Down to Last Value
 

Try..

If lstop 2 Then
Worksheets("Sheet2").Range("3:" & lstop + 1).FillDown
End If


"LostInNY" wrote:

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?

Fill Down to Last Value
 

Hi,
I'm still trying to work this one out, it looks like this code is meant to
copy whatever is on row 3 in sheet 2 down as many rows as you have entries
in sheet 1. Which means if you have 1 entry in sheet 1 you will have 2
entries in sheet 2. Try changing the +1 to +2.

Regards
Steve


"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?