Copying a formula down a column to the last row with data

[maclachlan]

I apologize if this questions has been asked before on the forum. I did a search but couldn't find anything. I have a spreadsheet with 3 columns.

Column A contains a data pull with the number of rows changing each time the data is refreshed.
Columns B and C contain a formula that needs to be copied from row 5 down the spreadsheet. I would like the formula to be copied 1 row up from the last data of column A

Example

_A_________B_____________C
5_567_____+A5*$A$1____+A5*$C$1
6_1064____+A6*$A$1____+A6*$C$1
7_579_____+A7*$A$1____+A7*$C$1
...
50_1068___+A50*$A$1___+A50*$C$50
51_End**_____no formulas on this line

Private Sub CommandButton1_Click()

Worksheets("Sheet4").Activate
ActiveWorkbook.Sheets(4).Range("b5:c5").Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet4").Range("b6", Range("a6",End(x1Down))


End Sub

 

[Norie]

Try this.

Private Sub CommandButton1_Click()
Dim LastRow as Long
With Worksheets("Sheet4")
     LastRow = .Range("A" & Rows.Count).End(xlUp).Row-1
     .Range("b5:c5").Copy .Range("b6:B" & LastRow)
End With
End Sub

 

[maclachlan]

Thanks Norie that works great