Type Mismatch when defining workbooks

PaulOPTC

New Member
Joined
Jan 13, 2022
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You're drilling down to the range level: wb1.Sheets("Link").Range("L8")
You would declare rng1 As Range, and rng2 As Range (or use whatever you need for range variable names) and Set rng1 (and rng2) to your range reference.
VBA Code:
Dim wb1 As Workbook
Dim rng1 As Range
Dim rng2 As Range
'
Set wb1 = ActiveWorkbook
Set rng1= wb1.Sheets("Link").Range("L8")
Set rng2 = wb1.Sheets("Link").Range("M8")
I stopped reading there so I hope that helps.

EDIT - if you really meant to define 3 workbooks then let us know.
 
Upvote 0
Are workbooks wb2 & wb3 already open?
 
Upvote 0
You're drilling down to the range level: wb1.Sheets("Link").Range("L8")
You would declare rng1 As Range, and rng2 As Range (or use whatever you need for range variable names) and Set rng1 (and rng2) to your range reference.
VBA Code:
Dim wb1 As Workbook
Dim rng1 As Range
Dim rng2 As Range
'
Set wb1 = ActiveWorkbook
Set rng1= wb1.Sheets("Link").Range("L8")
Set rng2 = wb1.Sheets("Link").Range("M8")
I stopped reading there so I hope that helps.

EDIT - if you really meant to define 3 workbooks then let us know.

Thank you for your reply, I did infact mean to define three work books.

The actual file path of the work books sit at wb1 Link L8 / M8
 
Upvote 0
They need to be, you cannot set a variable to a workbook unless it's open, however you assign the path & name to a string.

Okay I set the workbooks to open before the "Set wb2/3" so they will infact be open, now how should I point excel at the fact that I want to call it Wb2/3?
 
Upvote 0
What is the code that opens the workbooks?
 
Upvote 0
What is the code that opens the workbooks?
VBA Code:
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
'
Set wb1 = ActiveWorkbook



Sheets("Link").Visible = True
  Sheets("Link").Select

Name ActiveSheet.Range("K8") As _
    ActiveSheet.Range("L8")
    


Workbooks.Open (Range("L8"))

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

Set wb2 = wb1.Sheets("Link").Range("L8")  ' -<-- It breaks here'
Set wb3 = wb1.Sheets("Link").Range("M8")
 
Upvote 0
use
VBA Code:
Set wb2 = Workbooks.Open (Range("L8"))

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

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top