Type Mismatch when defining workbooks

[PaulOPTC]

Hello!

I am probably doing something wrong but I keep getting an error and I dont know how to fix it.

The long and the short of it is I have to copy data between three different excel workbooks

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
'
Set wb1 = ActiveWorkbook
Set wb2 = wb1.Sheets("Link").Range("L8")
Set wb3 = wb1.Sheets("Link").Range("M8")

Thats pretty much where I fail, I get a mismatch error.


Eventually my code will go and use

wb2.activate

wb1.activate

wb3.activate


Swapping back and fourth between them.



WB2 and WB3 have very particualr names, and their names are defined based on the Cells L8 and M8 in worksheet "Link" on work book 1


their names are derived from:

=SUBSTITUTE(E8,"Proposals\","Estimates\Bid Template.xlsb") which turns into

C:\Users\USER\Desktop\NAME BID TEMPLATE.xlsb

It has no trouble using the L8 to rename the workbook2 (which it ends up doing in the code) so it should see it as a legit path


Any ideas? Stupid mistake?

 

[PaulOPTC]

use

Set wb2 = Workbooks.Open (Range("L8"))

wb1.Activate
Sheets("Link").Select
Set wb3 = Workbooks.Open (Range("M8"))
wb1.Activate

Beautiful! It worked!

Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.