Prev: Sharing Macros
Next: Macro to run based on criteria
From: LostInNY on 7 Jun 2010 11:11
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?
From: Rick Rothstein on 7 Jun 2010 11:23
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" <LostInNY(a)discussions.microsoft.com> wrote in message
news:D7AD520B-BB96-4CAF-86BD-5897823DC204(a)microsoft.com...
> 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?

From: Jackpot on 7 Jun 2010 11:24
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?
 | 
Pages: 1
Prev: Sharing Macros
Next: Macro to run based on criteria