I have a variable number of rows in Sheet1, starting on row 2 ( Row 1
contains a column header ). Lets say for example this time it is 500 rows. (
lastrow = 501 ). On Sheet 2 I have a column header in cell a1 and an
existing formula in A2. How do I copy the formula down based on the lastrow
value from Sheet 1. In this example, the macro would copy the formula down
to A501.

Thanks in advance for your help,
Joe

Basic code looks like this

Sub test()
Dim LastRow As Long
With Worksheets("Sheet2")
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
.Range("A2").AutoFill Destination:=.Range("A2:A" & LastRow) _
, Type:=xlFillDefault
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"adidas12121" <(E-Mail Removed)> wrote in message news:09B20015-E122-4A36-94B6-(E-Mail Removed)...
>I have a variable number of rows in Sheet1, starting on row 2 ( Row 1
> contains a column header ). Lets say for example this time it is 500 rows. (
> lastrow = 501 ). On Sheet 2 I have a column header in cell a1 and an
> existing formula in A2. How do I copy the formula down based on the lastrow
> value from Sheet 1. In this example, the macro would copy the formula down
> to A501.
>
> Thanks in advance for your help,
> Joe

Dim LastRow as long
with worksheets("Sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("A2:A" & lastrow).formular1c1 _
= worksheets("Sheet2").range("a2").formular1c1
end with

But if you use column A to find the last row, then aren't you overwriting the
stuff in that range?


adidas12121 wrote:
>
> I have a variable number of rows in Sheet1, starting on row 2 ( Row 1
> contains a column header ). Lets say for example this time it is 500 rows. (
> lastrow = 501 ). On Sheet 2 I have a column header in cell a1 and an
> existing formula in A2. How do I copy the formula down based on the lastrow
> value from Sheet 1. In this example, the macro would copy the formula down
> to A501.
>
> Thanks in advance for your help,
> Joe

--

Dave Peterson

Hi Ron,
PERFECT Solution !
Your code worked perfect the very first time I used it.
Thank you very much for saving me so much time, I appreciate it
Have a great day,
Joe

"Ron de Bruin" wrote:

> Basic code looks like this
>
> Sub test()
> Dim LastRow As Long
> With Worksheets("Sheet2")
> LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
> .Range("A2").AutoFill Destination:=.Range("A2:A" & LastRow) _
> , Type:=xlFillDefault
> End With
> End Sub
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "adidas12121" <(E-Mail Removed)> wrote in message news:09B20015-E122-4A36-94B6-(E-Mail Removed)...
> >I have a variable number of rows in Sheet1, starting on row 2 ( Row 1
> > contains a column header ). Lets say for example this time it is 500 rows. (
> > lastrow = 501 ). On Sheet 2 I have a column header in cell a1 and an
> > existing formula in A2. How do I copy the formula down based on the lastrow
> > value from Sheet 1. In this example, the macro would copy the formula down
> > to A501.
> >
> > Thanks in advance for your help,
> > Joe
>