VBA Autofill Formula to last row w data

[Littlemalky]

Hey, so I have this line of code that seems like it should work, I don't get any kind of error when I run the macro; however, when I check my file the only part of it that worked was the copying and pasting the formula into the single cell. Even though I have the autofill language in there, it doesn't seem to be applying it to the column. Here's what I have:

Range("D2").Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
    With Range("S2")
        .AutoFill Destination:=Range("S2", Cells(Cells(Rows.Count, 20).End(xlUp).Row, 19))
    End With

I just need it to autofill down to the last row with data in it. I have data in columns A:V if that's relevant, but I only want to autofill column "S".

 

[VoG]

Perhaps

Range("D2").Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
    With Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
        .AutoFill Destination:=Range("S2", .Cells(Cells(Rows.Count, 20).End(xlUp).Row, 19))
    End With

 

[MrKowz]

You almost have it. When using Range() in that fashion (designating the first cell in the range in the first argument, the last cell in the range in the second argument), you need to use the Cells() function inside the range in each argument:

Range("D2").Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
    With Range("S2")
        .AutoFill Destination:=Range([COLOR=red][B]Cells(2,19),[/B][/COLOR]Cells(Cells(Rows.Count, 20).End(xlUp).Row, 19))
    End With

 

[Littlemalky]

VoG, that didn't work, it gave me an error.

MrKowz, that makes sense, and I applied that exact line of code. I didn't get any kind of error, but when I look at my file, it still hasn't copied the formula all the way down. I feel like there is one little tweak I'm missing.

 

[VoG]

Maybe

Range("D2").Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
    With Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
        .AutoFill Destination:=Range(.Cells(2, 19), .Cells(Cells(Rows.Count, 20).End(xlUp).Row, 19))
    End With

 

[Littlemalky]

Tried that too =/

 

[VoG]

Try

Range("D2").Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
    With Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("S2").AutoFill Destination:=.Range(.Cells(2, 19), .Cells(LR, 19))
    End With

 

[MrKowz]

Ahh - the Range references weren't linked to that secondary workbook:

Range("D2").Copy _
        Destination:=Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add").Range("S2")
With Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx").Worksheets("detail w add")
    .Range("S2").AutoFill Destination:=.Range(.Cells(2, 19), .Cells(Cells(Rows.Count, 20).End(xlUp).row, 19))
End With

or to make the code a bit easier to read and manipulate:

Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx")
Set ws = wb.Worksheets("detail w add")
Range("D2").Copy _
        Destination:=ws.Range("S2")
With ws
    .Range("S2").AutoFill Destination:=.Range(.Cells(2, 19), .Cells(Cells(Rows.Count, 20).End(xlUp).row, 19))
End With

 

[Littlemalky]

This is getting embarrassing. I feel like these should all be working because they are logical...yet again, still no error, but it's not applying it. I'm so confused...I feel like it's something simple I'm missing.

MrKowz, I just tried your:

Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Domestic" & ".xlsx")
Set ws = wb.Worksheets("detail w add")
Range("D2").Copy _
        Destination:=ws.Range("S2")
With ws
    .Range("S2").AutoFill Destination:=.Range(.Cells(2, 19), .Cells(Cells(Rows.Count, 20).End(xlUp).row, 19))
End With

 

[VoG]

Maybe

.Range("S2").AutoFill Destination:=.Range(.Cells(2, 19), .Cells(.Cells(Rows.Count, 20).End(xlUp).Row, 19))