| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Switching between Workbooks | Excel Discussion (Misc queries) | |||
| Switching between workbooks | Excel Programming | |||
| Switching between workbooks | Excel Discussion (Misc queries) | |||
| Switching Between 2 workbooks using VBA | Excel Programming | |||
| switching between workbooks... | Excel Programming | |||
Linear Mode
