CODEWITHSUNDEEP
vbaexcelexcel-2007
kmiao91
kmiao91

Reputation: 313

VBA Dragging down formulas (in multiple rows) to last row used

I have formulas from columns O -> X and need them drag them to last row used. Below is the current code I am using:

Dim wkb As Workbook
Dim wkbFrom As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim path As String, FilePart As String
Dim TheFile
Dim loc As String
Dim Lastrow As Long

Set wkb = ThisWorkbook
loc = shPivot.Range("E11").Value
path = shPivot.Range("E12").Value
FilePart = Trim(shPivot.Range("E13").Value)
TheFile = Dir(path & "*" & FilePart & ".xls")
Set wkbFrom = Workbooks.Open(loc & path & TheFile & FilePart)
Set wks = wkbFrom.Sheets("SUPPLIER_01_00028257_KIK CUSTOM")
Set rng = wks.Range("A2:N500")

'Copies range from report generated to share drive and pastes into the current week tab of open order report
rng.Copy wkb.Sheets("Current Week").Range("A4")

With ActiveSheet

    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("O4:X4").AutoFill .Range("O4:X4").Resize(Lastrow)

End With

The code Lastrow is not dragging the formulas down

Upvotes: 6

Views: 120130

Answers (2)

Geographos
Geographos

Reputation: 1496

I used this:

Sub sum_1to10_fill()
Sheets("13").Activate
Range("EG12").Copy
Range("EG12:FT36").PasteSpecial xlPasteFormulas

End sub

...and filled up my whole label both down and right.

Upvotes: 0

Mike Kellogg
Mike Kellogg

Reputation: 1178

You can do auto-fill like this in VBA (verified using macro recording)

Range("O1:X1").Select
Selection.AutoFill Destination:=Range("O1:X25"), Type:=xlFillDefault

Now that you have this code as a base to work with you can use any variables you like in the syntax like this:

Range("O1:X1").Select
Selection.AutoFill Destination:=Range("O1:X" & Lastrow), Type:=xlFillDefault

Upvotes: 20

Related Questions