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 |