CODEWITHSUNDEEP
excelruntime-errorautofillvba
Sky
Sky

Reputation: 33

Code works in Module but not in worksheets Error 1004

Was hoping to recieve help on this bit of code below. It works in module 1, however does not work in any of the worksheets. I've tried my best with my limited knowledge but haven't been able to fix it.

Sub lastrow()
    Dim MCFPSheet As Worksheet
    Set MCFPSheet = ThisWorkbook.Worksheets("MCFP Referrals YTD")

    Dim lastrow As Long
    lastrow = MCFPSheet.Range("I2").End(xlDown).Row

    With MCFPSheet.Range("R8")
        .AutoFill Destination:=Range("R8:R" & lastrow&)
    End With

    With MCFPSheet.Range("S2")
        .AutoFill Destination:=Range("S2:S" & lastrow&)
    End With

    With MCFPSheet.Range("T2")
        .AutoFill Destination:=Range("T2:T" & lastrow&)
    End With

    With MCFPSheet.Range("U2")
        .AutoFill Destination:=Range("U2:U" & lastrow&)
    End With

    With MCFPSheet.Range("V2")
        .AutoFill Destination:=Range("V2:V" & lastrow&)
    End With

    With MCFPSheet.Range("W2")
        .AutoFill Destination:=Range("W2:W" & lastrow&)
    End With
End Sub

I receive

error 1004

in .AutoFill Destination:=Range("R8:R" & lastrow&) line.

Upvotes: 1

Views: 107

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

According to @Sixthsense comment that you need to specify the worksheet for your destination, also all these With statements don't make much sense if they are all one-liners.

It would be much shorter using the With that way:

Sub lastrow()
    Dim MCFPSheet As Worksheet
    Set MCFPSheet = ThisWorkbook.Worksheets("MCFP Referrals YTD")

    Dim lastrow As Long
    lastrow = MCFPSheet.Range("I2").End(xlDown).Row

    With MCFPSheet
        .Range("R8").AutoFill Destination:=.Range("R8:R" & lastrow)
        .Range("S2").AutoFill Destination:=.Range("S2:S" & lastrow)
        .Range("T2").AutoFill Destination:=.Range("T2:T" & lastrow)
        .Range("U2").AutoFill Destination:=.Range("U2:U" & lastrow)
        .Range("V2").AutoFill Destination:=.Range("V2:V" & lastrow)
        .Range("W2").AutoFill Destination:=.Range("W2:W" & lastrow)
    End With
End Sub

Note that Destination:=.Range now also refers to the MCFPSheet sheet by using the leading . before Range!
And I also removed the & from lastrow& where I don't see any sense in it.

Upvotes: 1

Related Questions