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?

 

[Micron]

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.

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.

 

[Fluff]

Are workbooks wb2 & wb3 already open?

 

[PaulOPTC]

Are workbooks wb2 & wb3 already open?

They are not,

I have Workbooks.Open (Range("L8"))

(Which does work)

and

Workbooks.Open (Range("M8"))

Later in the code.

 

[PaulOPTC]

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.

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

 

[Fluff]

They are not,

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.

 

[PaulOPTC]

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?

 

[Fluff]

What is the code that opens the workbooks?

 

[PaulOPTC]

What is the code that opens the workbooks?

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")

 

[Fluff]

use

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

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