Open workbook via filepath within a cell

Question

Workbook x is open.

I want to:

Open Excel workbook y (the filepath is in cell "B2" of the macro tab of workbook x).
Copy A3:A26 from macro tab of workbook x, and paste into M41:M63 in the summary tab of workbook y.

The problem is with the B2 filepath name. I think I need to specify to pull the text from B2 to open the correct workbook.

The error message is

Sorry, we couldn't find B2.xlsx

Sub Foo()

Dim x As Workbook
Dim y As Workbook

Set y = Workbooks.Open("B2")

x.Sheets("Macro").Range("A3:A26").Copy

y.Sheets("Summary").Range("M41:M63").PasteSpecial

End Sub

JohnM · Accepted Answer · 2022-10-31 20:33:46Z

1

Try the following ... assumes the Sheet in Workbook x is actually called "Macro" (you also have a Sheet in Workbook y with the same name?) ... you also need to set the value for x which this does:

Sub Foo()
    Dim x As Workbook
    Dim y As Workbook
    Set x = ActiveWorkbook
    Set y = Workbooks.Open(x.Sheets("Macro").Range("B2"))
    x.Sheets("Macro").Range("A3:A26").Copy
    y.Sheets("Summary").Range("M41:M63").PasteSpecial
End Sub

answered Oct 31, 2022 at 20:33

JohnM

3,3904 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Open workbook via filepath within a cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related