Open Excel via Filepath within a cell-CodePudding

Can someone please assist me with this macro? Here's what I'm trying to accomplish:

Workbook x is currently open. Open Excel spreadsheet 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 I'm running into is with the B2 filepath name. I think I need to specify to pull the text from B2 to open the correct workbook but I can't quite figure that out. The error message I'm getting is "Sorry, we couldn't find B2.xlsx". Thanks in advance!

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

CodePudding user response：

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