Copy and paste in VBA

A

AM

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")
 
R

Ron de Bruin

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
 
T

Tom Ogilvy

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
 
Top