I have two excel documents. I'm trying to write a VBA code to simply copy a bunch of cells from the first workbook to the second workbook. The following code works when it is in a VBA code module, and I run the code by pressing F8. This Macro2() is in the General option.

Sub Macro2()
    Workbooks("Feeder document for quoting.xlsm").Worksheets("Factor").Range("A4:AE34").Copy _
        Destination:=Workbooks("Cost-Sell Sheet.xlsm").Worksheets("RFJN").Range("A4:AE34")
End Sub

But when I call this macro from another macro, I get "Application-defined or object-defined error", which I don't fully understand. This other macro is triggered by a command button.

I have two excel documents. I'm trying to write a VBA code to simply copy a bunch of cells from the first workbook to the second workbook. The following code works when it is in a VBA code module, and I run the code by pressing F8. This Macro2() is in the General option.

Sub Macro2()
    Workbooks("Feeder document for quoting.xlsm").Worksheets("Factor").Range("A4:AE34").Copy _
        Destination:=Workbooks("Cost-Sell Sheet.xlsm").Worksheets("RFJN").Range("A4:AE34")
End Sub

But when I call this macro from another macro, I get "Application-defined or object-defined error", which I don't fully understand. This other macro is triggered by a command button.

• excel
• vba
• copy
• paste

• Where is the macro located ? module, workbook, worksheet, personal workbook ? What is the line of code in the other macro that is in error – CDP1802 Commented Mar 25 at 16:50
• The workbooks are not set, when the macro is called there is no file to work on. Try and add Dim WbSource as Workbook: Set WbSource = Workbooks("Feeder document for quoting.xlsm") and Dim WbDestination as Workbook: Set WbDestination = Workbooks("Cost-Sell Sheet.xlsm") (fix with the filepaths) see here – Oran G. Utan Commented Mar 25 at 17:06
• @OranG.Utan but you don't need Workbook variables if those files are open, right? – BigBen Commented Mar 25 at 18:41
• True, and actually the above works fine for me both ways: when running the copy/paste alone and when calling it from another procedure. – Oran G. Utan Commented Mar 25 at 19:12

1 Answer 1

You didn't have lines to open either workbooks

Try to have some line like:

workbook.open (Your Workbook Location)
---------------------------------------------------
EDIT: below

Sub CopyB()

Dim WB0 As Workbook
Set WB0 = ThisWorkbook

Dim WB1 As Workbook
Set WB1 = Workbooks.Open("E:\DESKTOP\Import\b1.xlsx")

Dim WS0 As Worksheet
Set WS0 = WB0.Sheets("ABC")

Dim RNG As Range
Set RNG = WS0.Range("A1:B2")

        RNG.Copy Destination:=WB1.Sheets(1).Range("A1")

End Sub