(Solved) – Can’t get AutoFill to Drag down to last Row


The workbook I’m working on has 16 Worksheets. Starting with Sheet2 (Report1) before I loop it through the other worksheets.

When I open the Workbook, it opens on Sheet1 (TOC), and I’m trying to run the Macro from here.

On Sheet2, Rows 1-3 is Header data.

-I entered a formula into F4, but I can’t seem to get a formula AutoFill down Column F to the last row based on of data based on Column A. (There can be blank Cells in Column F)

-Then I would like take the formula from F4, copy/paste to K4, and Autofill down Column K as well.

(Now if I’m actually IN Worksheet2, it works??) Do I need to move to each Active Sheet in order to get this to work?

This is what I currently have:

Sub Test()

'Turn Off Screen Updates
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Dim ws As Worksheet
    Set ws = Worksheets("Report1")

'Update $ - % Chg formula
    ws.Range("F4").Formula = "=(E4/(E4-G4))-1"
    ws.Range("F4").NumberFormat = "0.0%"
    Range("F4").AutoFill Destination:=Range("F4:F" & Range("A" & Rows.Count).End(xlUp).Row)

'Update Units - % Chg Formula
    ws.Range("F4").Copy Range("K4")
    Range("K4").AutoFill Destination:=Range("K4:K" & Range("A" & Rows.Count).End(xlUp).Row)

'Turn On Screen Updates
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *