Application-defined or object-defined error VBA Copy between workbooks

Question

Sub Macro1()

Dim for_S3 As Long, d As Long
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook

   Set wb1 = Workbooks("1.xlsx")
   Set wb2 = Workbooks("2.xlsx")
   Set wb3 = Workbooks("3 Final.xlsm")

        d = 3
    For for_S3 = 1 To 12
        Windows("3 Final.xlsm").Activate
        Workbooks("3 Final.xlsm").Worksheets("Sheet 2").Range(Cells(2, d), Cells(65, d)).Copy

        wb2.Worksheets("RAW MATERIALS PLANNER_ALL").Range("C5").PasteSpecial Paste:=xlPasteValues

        wb2.Worksheets("RAW MATERIALS PLANNER_ALL").Range("I3:I5000").Copy

        wb3.Worksheets("Sheet 3").Range(Cells(2, d), Cells(65, d)).PasteSpecial Paste:=xlPasteValues, 
        Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        d = d + 1
    Next

End Sub

Can anyone help me figure out what's wrong with the codes? It's showing

runtime error "1004" Application-defined or object-defined error

for the line Workbooks("3 Final.xlsm").Worksheets("Sheet 2").Range(Cells(2, d), Cells(65, d)).Copy.

I really dont see what's wrong with it.

Your `Cells` references arent qualified, so they are referring to the `ActiveSheet` instead of the one you want. — braX, Jan 28 '20 at 04:52

jcrizk · Answer 1 · 2020-01-28T05:25:13.200

I'm not sure why you're using PasteSpecial, so I'm just going to ignore it until I'm aware of a reason.

I tried this out in a workbook, and it works, aside from the filepath that I made anonymous. See if you can get what you want with the help of the below code, but if not then let me know.

Public Sub XferBetweenWorkbooks()
    Dim filepath As String
    Dim otherwb As Excel.Workbook
    Dim thisWs As Worksheet
    Dim otherWs As Worksheet

    'im assuming that the workbook isn't open, so this is how you would open a closed
    'workbook and set a reference to it
    filepath = "C:\some\file\path\here\otherthing.xlsx"
    Set otherwb = Excel.Workbooks.Open(filepath)

    'set a reference to the 1st worksheet in the workbook that you just opened.
    'alternatively, you can use the worksheet's name also
    Set otherWs = otherwb.Worksheets(1)

    'set a reference to the 1st worksheet in the workbook that you're working in.
    'alternatively, you can use the worksheet's name also
    Set thisWs = ThisWorkbook.Worksheets(1)

    'this will transfer the values from A1:A3 in 'otherwb' to A1:A3 in the workbook you're working in
    thisWs.Range(thisWs.Cells(1, 1), thisWs.Cells(3, 1)).Value = otherWs.Range(otherWs.Cells(1, 1), otherWs.Cells(3, 1)).Value

    'closes the workbook that you opened at the start
    otherwb.Close
End Sub

Application-defined or object-defined error VBA Copy between workbooks

1 Answers1