Error when run a Macro in different worksheet

Question

I write some code that only works within worksheet 1.

Data inputs are from worksheet 2.

If I run the Macro when worksheet 1 is active, the code works fine.

However, if I run it with a button in worksheet 3 (as dashboard), it will show an error message:

Run-time error '1004' Method 'Range' of object '_worksheet' failed.

Sub processsap()


 Dim SAPData As Worksheet
 Dim SAP As Worksheet
 Dim lr As Long

 Set SAPData = Worksheets("SAP Data")
 Set SAP = Worksheets("SAP")

     lr = SAPData.Range("A10000").End(xlUp).Row


 With SAP

        .UsedRange.ClearContents

        .Range("A1") = "='SAP Data'!B2"
        .Range("B1") = "='SAP Data'!C2"
        .Range("C1") = "=RIGHT(LEFT(G1,15),10)"
        .Range("D1") = "='SAP Data'!V2"
        .Range("E1") = "='SAP Data'!Q2"
        .Range("F1") = "=-99999"
        .Range("G1") = "='SAP Data'!D2"
        .Range("H1") = "='SAP Data'!O2"
        .Range("L1") = "='SAP Data'!E2"
        .Range("M1") = "='SAP Data'!F2"
        .Range("N1") = "='SAP Data'!G2"
        .Range("O1") = "='SAP Data'!M2"
        .Range("Q1") = "=IF('SAP Data'!N2=""XXXX"",""XT71"",'SAP Data'!N2)"
        .Range("T1") = "='SAP Data'!S2"
        .Range("U1") = "='SAP Data'!W2"
        .Range("AB1") = "=AJ1"
        .Range("AC1") = "=AK1"
        .Range("AF1") = "=AJ1"
        .Range("AG1") = "=AK1"
        .Range("AJ1") = "=VLOOKUP(AS1,FXrates!B:D,2,FALSE)*AK1"
        .Range("AK1") = "='SAP Data'!AD2"
        .Range("AL1") = "=AJ1"
        .Range("AS1") = "='SAP Data'!K2"
        .Range("AV1") = "=IF(D1=""IC RC - SLUITREKENING"",111983780,VLOOKUP(G1,'\\insim.biz\wpsgroups\120132\Finance\ART\2017\10\SCF\[DF ART BU Delivery SCFIN002 20171031 Macro version_CPTY V8.1 - w CPT - Copy.xlsm]Position'!$G:$AM,33,FALSE))"
        .Range("BL1") = "='SAP Data'!J2"
        .Range("CF1") = "='SAP Data'!P2"
        .Range("CJ1") = "='SAP Data'!R2"
        .Range("CW1") = "NO_GUARANTEE"
        .Range(Cells(1, 1), Cells(lr - 1, 110)).FillDown
 End With

End Sub

Do you know the reason?

Thanks

I have pasted my code above, the error message is only for this line: ".Range(Cells(1, 1), Cells(lr - 1, 110)).FillDown" — Gong

Unknown Unknown · Accepted Answer · 2018-09-10T08:20:05

.Range(Cells(1, 1), Cells(lr - 1, 110)).FillDown

... should be,

.Range(.Cells(1, 1), .Cells(lr - 1, 110)).FillDown

Error when run a Macro in different worksheet

1 Answers