switching between workbooks
 

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

switching between workbooks
 

In the code, you have the SourceWkbk in quotes

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


"Jay" wrote:

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

switching between workbooks
 

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" wrote:

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

switching between workbooks
 

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

"JLGWhiz" wrote:

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" wrote:

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

switching between workbooks
 

Well, in that case, try:

Workbooks("DestWkbk").Activate

"Jay" wrote:

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

"JLGWhiz" wrote:

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" wrote:

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

switching between workbooks
 

Ddisregard that. I'm looking at the code.

"Jay" wrote:

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

"JLGWhiz" wrote:

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" wrote:

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

switching between workbooks
 

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

"Jay" wrote:

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

"JLGWhiz" wrote:

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" wrote:

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

switching between workbooks
 

On Oct 30, 9:49*am, Jay wrote:
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

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

switching between workbooks
 

Yes, that worked.
thank you!

"JLGWhiz" wrote:

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

"Jay" wrote:

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

"JLGWhiz" wrote:

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" wrote:

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