switching between workbooks

[Jay]

Why is the following code not allowing me to switch between workbooks?
I get a Run-time error '13':
Type mismatch
on line
Workbooks(SourceWkbk).Activate

Sub Copy_WOs ()

Dim DestWkbk As Workbook

If Range("I2") = "" Then
MsgBox "Enter Person Reporting in Cell I2"
Exit Sub
ElseIf Range("I2") = "Jay" Then
Set DestWkbk = Workbooks.Open("H:\FAC\JayProjTimeTracking.xls",
UpdateLinks:=False, ReadOnly:=True)
ElseIf Range("I2") = "Dave" Then
Set DestWkbk = Workbooks.Open("H:\FAC\Dave
Sipes\DavProjTimeTracking.xls", UpdateLinks:=False, ReadOnly:=True)
Else
MsgBox "Person Reporting name mispelled (or is in all caps)"
Exit Sub
End If

Workbooks("SourceWkbk.xls").Activate
Range("F6:F12").Copy
Workbooks(DestWkbk).Activate
Range("D:7").Select
Activesheet.Paste

End Sub

 

[dmoney]

In the code, you have the SourceWkbk in quotes

I am assuming that it is refering to a variable named SourceWkbk

 

[JLGWhiz]

I am guessing that somewhere you have assigned SourceWkbk as a variable for
the actual name of the workbook, but in this macro you are using quotation
marks to enclose it as a string file name.

 

[Jay]

Oops, sorry.
What I meant was it's erroring out on the line
Workbooks(DestWkbk).Activate

 

[JLGWhiz]

Well, in that case, try:

Workbooks("DestWkbk").Activate

 

[JLGWhiz]

Ddisregard that. I'm looking at the code.

Oops, sorry.
What I meant was it's erroring out on the line
Workbooks(DestWkbk).Activate

 

[JLGWhiz]

I believe you want to just use DestWkbk without the Workbooks qualifier. You
Set that as an object variable with the Workbooks.Open statement.

 

[geoff_ness]

Why is the following code not allowing me to switch between workbooks?
I get a Run-time error '13':
Type mismatch
on line
Workbooks(SourceWkbk).Activate

Sub Copy_WOs ()

   Dim DestWkbk As Workbook

   If Range("I2") = "" Then
        MsgBox "Enter Person Reporting in Cell I2"
        Exit Sub
      ElseIf Range("I2") = "Jay" Then
            Set DestWkbk = Workbooks.Open("H:\FAC\JayProjTimeTracking.xls",
UpdateLinks:=False, ReadOnly:=True)
      ElseIf Range("I2") = "Dave" Then
            Set DestWkbk = Workbooks.Open("H:\FAC\Dave
Sipes\DavProjTimeTracking.xls", UpdateLinks:=False, ReadOnly:=True)
      Else
            MsgBox "Person Reporting name mispelled (or is inall caps)"
            Exit Sub
    End If

    Workbooks("SourceWkbk.xls").Activate
    Range("F6:F12").Copy
    Workbooks(DestWkbk).Activate
    Range("D:7").Select
    Activesheet.Paste

End Sub

If SourceWkbk is an object variable referring to a workbook, there's
no need to use the Workbooks collection - you can just go to the
variable itself and call its activate method, i.e.
SourceWkbk.Activate

Similarly you can call
DestWkbk.Activate

When you refer to the workbooks collection, the argument in
parentheses should be a string containing the workbook's name - if the
workbook is called "SourceWkbk.xls" then
Workbooks("SourceWkbk.xls").Activate
will work if SourceWkbk.xls is open.
Alternatively if SourceWkbk is a string variable containing the
workbook's name, then
Workbooks(SourceWkbk).Activate will work if SourceWkbk is open.

HTH

 

[Jay]

Yes, that worked.
thank you!

I believe you want to just use DestWkbk without the Workbooks qualifier. You
Set that as an object variable with the Workbooks.Open statement.