Copy and paste in VBA
 

I want to copy the formula in E7 down to the number of
rows that contain data in columns A through D. But this
number may vary. In this example the last row with data
will be 12,073. How can I get VBA to take this into
account?


Range("E7").Formula = "=Sum(B7:D7)"
Range("E7").Copy Destination:=oWorksheet.Range("E8:E12073")

Copy and paste in VBA
 

Hi AM

This example will filldown till the last value in D

Sub test()
Dim LastRow As Long
With Worksheets("Sheet2")
.Range("E7").Formula = "=Sum(B7:D7)"
LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
.Range("E7").AutoFill Destination:=.Range("E7:E" & LastRow) _
, Type:=xlFillDefault
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AM" wrote in message ...

I want to copy the formula in E7 down to the number of
rows that contain data in columns A through D. But this
number may vary. In this example the last row with data
will be 12,073. How can I get VBA to take this into
account?


Range("E7").Formula = "=Sum(B7:D7)"
Range("E7").Copy Destination:=oWorksheet.Range("E8:E12073")

Copy and paste in VBA
 

Dim rng as Range
set rng = cells(rows.count,4).End(xlup)(1,2)
Range(range("E7"),rng).Formula = "=Sum(B7:D7)"


as an example in the immdiate window:
set rng = cells(rows.count,4).End(xlup)(1,2)
? range(range("E7"),rng).Address
$E$7:$E$20

--
Regards,
Tom Ogilvy



"AM" wrote in message
...

I want to copy the formula in E7 down to the number of
rows that contain data in columns A through D. But this
number may vary. In this example the last row with data
will be 12,073. How can I get VBA to take this into
account?


Range("E7").Formula = "=Sum(B7:D7)"
Range("E7").Copy Destination:=oWorksheet.Range("E8:E12073")