我无法在我的vba代码中正确使用自动填充方法

This code gives me error 1004 and autofill method error If I remove first autofill method then 2nd autofill is working fine otherwise It gives an error,

此代码给出了错误1004和自动填充方法错误如果我删除第一个自动填充方法然后第二个自动填充工作正常，否则它给出一个错误，

Sub w()

    Set ws1 = ActiveSheet
    ws1.Columns("A:A").Insert Shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove
    ws1.Range("A1").Value = "S.No"
    ws1.Range("A1").Interior.Color = RGB(204, 204, 204)
    LastRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    ws1.Range("A2").Select ws1.Range("A2").FormulaR1C1 = _
        "=TEXT(RC[1],""000000000000"")&""-""&TEXT(RC[2],""0000"")"
    ws1.Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A" & LastRow), Type:=xlFillDefault
    ws1.Range("A2:A" & LastRow).Select
    Set ws2 = Workbooks("Closing Asset Listing-Leading Ledger_Aug-17.xlsx").Worksheets(1)
    LastRowclosing = ws2.Cells(Rows.Count, "B").End(xlUp).Row

    ws2.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    ws2.Range("A2").FormulaR1C1 = _
        "=TEXT(RC[6],""000000000000"")&""-""&TEXT(RC[7],""0000"")"

    ws2.Range("A2").AutoFill Destination:=Range("A2:A" & LastRowclosing), Type:=xlFillDefault
    Range("A2:A" & LastRowclosing).Select
    ws1.Range("AB2").Select
    ws1.Range("AB2").FormulaR1C1 = _
        "=VLOOKUP(RC[-27],'[Closing Asset Listing-Leading Ledger_Aug-17.xlsx]Sheet1'!R2C1:R13C12,12,0)"

    ws1.Range("AB2").AutoFill Destination:=Range("L2:L" & LastRow), Type:=xlFillDefault

    Range("AB2" & LastRow).Select

End Sub

1 个解决方案

#1

You are trying to autofill to a range on a different sheet. Your code should be:

您正尝试自动填充到不同工作表上的范围。你的代码应该是：

ws2.Range("A2").AutoFill Destination:=ws2.Range("A2:A" & LastRowclosing), Type:=xlFillDefault