Select.Autofill Destination:=Range NOT autofilling-CodePudding

I have been working with VBA for about two years now, and I am having a problem I haven't had before. I am trying to autofill columns A, B, D, E and G with formulas based on the number of cells that are populated in column L. In the example shown below, cells L2-L5 are populated. With my autofill formulas, this should mean that cells A2-A5 populate, cells B2-B5 populate etc. But they don't. Only column G populates correctly, and I am stumped as to why this is happening. Any brilliant ideas?

Here is my code:

Application.DisplayAlerts = False

Sheets("QBTimecard").Select
    Range("DP2").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-119]&""_""&RC[-107]&""_""&IF(RC[-2]<>""Client Transportation"",""Z"",""XY"")"
    Range("DQ2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-13]<=RC[-8],""Pass"",""MMV"")"
    On Error Resume Next
    Selection.Autofill Destination:=Range("dp2:ds" & Cells(Rows.Count, "A").End(xlUp).Row)

Sheets("QBTimecard").Select
    Columns("DP:DP").Select
    Selection.Copy
Sheets("PunchEntryImport").Select
    Columns("L:L").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Blank"
    Range("L2").Select

 Sheets("PunchEntryImport").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(QBTimecard!C,MATCH(PunchEntryImport!RC[11],QBTimecard!C[119],0))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(QBTimecard!C[118],PunchEntryImport!RC[10],QBTimecard!C[13])/COUNTIF(QBTimecard!C[118],PunchEntryImport!RC[10])"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Lookup!C[-2],MATCH(INDEX(QBTimecard!C[9],MATCH(PunchEntryImport!RC[8],QBTimecard!C[116],0)),Lookup!C[-3],0))&""/1/""&INDEX(QBTimecard!C[10],MATCH(PunchEntryImport!RC[8],QBTimecard!C[116],0))&"" ""&TEXT(INDEX(QBTimecard!C[12],MATCH(PunchEntryImport!RC[8],QBTimecard!C[116],0)),""HHMM"")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Lookup!C[-3],MATCH(INDEX(QBTimecard!C[8],MATCH(PunchEntryImport!RC[7],QBTimecard!C[115],0)),Lookup!C[-4],0))&""/1/""&INDEX(QBTimecard!C[9],MATCH(PunchEntryImport!RC[7],QBTimecard!C[115],0))&"" ""&TEXT(INDEX(QBTimecard!C[12],MATCH(PunchEntryImport!RC[7],QBTimecard!C[115],0)),""HHMM"")"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(QBTimecard!C[103],MATCH(PunchEntryImport!RC[5],QBTimecard!C[113],0))"
   On Error Resume Next

'This is the portion that is not autofilling   
   Selection.Autofill Destination:=Range("a2:a" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("b2:b" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("d2:d" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("e2:e" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("g2:g" & Cells(Rows.Count, "L").End(xlUp).Row)

CodePudding user response：

Without re-writing the whole thing, this is a more-approachable way to handle this, without the Select/ Activate/ Autofill:

Dim wsQBTC As Worksheet, wsPE As Worksheet, lr As Long

Set wsQBTC = Worksheets("QBTimecard")
Set wsPEI = Worksheets("PunchEntryImport")

lr = wsQBTC.Cells(Rows.Count, "A").End(xlUp).Row
With wsQBTC
    .Range("DP2:DP" & lr).FormulaR1C1 = "=RC[-119]&""_""&RC[-107]&""_""&IF(RC[-2]<>""Client Transportation"",""Z"",""XY"")"
    .Range("DQ2:DQ" & lr).FormulaR1C1 = "=IF(RC[-13]<=RC[-8],""Pass"",""MMV"")"
End With

CodePudding user response：

From the help for Range.AutoFill

The destination must include the source range.

Because you are using Selection.AutoFill and G2 is the active cell, the autofill will only work when G2 is part of the range being autofilled.

To fix this, replace Selection.AutoFill with Range("A2").AutoFill, Range("B2").AutoFill and so on