PDA

View Full Version : Referencing Other Open Workbooks.



paddysheeran
04-21-2009, 09:32 AM
Hi All,

cant seem to get the code below to work. When the two sheets are in the same workbook the code works fine but once i try to reference it from different workbooks I get a Run-time error '13': Type mismatch code.

any help - its probably something simple.

Sub ResolverClosed_Update_SLA_ResolvedCol()

Dim R As Range
Dim Value As Integer
Dim CEC As Worksheets
Dim Template As Worksheets
Set CEC = Application.Workbooks("CEC_Calculations.xls").Worksheets("Resolver_Closed")
Set Template = Application.Workbooks("CEC Weekly Performance Report -Template.xls").Worksheets("Resolver - Priority (Closed)")
CEC.Select
Template.Range("L6").Select
Do
Set R = CEC.Range("K4:L2000").Find(ActiveCell.Offset(0, -10))
If Not R Is Nothing Then ActiveCell = R.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -10))
End Sub

thanks.

paddy.

Bob Phillips
04-21-2009, 10:28 AM
Sub ResolverClosed_Update_SLA_ResolvedCol()

Dim R As Range
Dim Value As Integer
Dim CEC As Worksheet
Dim Template As Worksheet
Set CEC = Application.Workbooks("CEC_Calculations.xls").Worksheets("Resolver_Closed")
Set Template = Application.Workbooks("CEC Weekly Performance Report -Template.xls").Worksheets("Resolver - Priority (Closed)")
CEC.Select
Template.Range("L6").Select
Do
Set R = CEC.Range("K4:L2000").Find(ActiveCell.Offset(0, -10))
If Not R Is Nothing Then ActiveCell = R.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -10))
End Sub

paddysheeran
04-22-2009, 02:26 AM
Hi this works until the line:

Template.Range("L6").Select

I then get the error message:

Run-time error '1004':
Select method of Range class failed

Bob Phillips
04-22-2009, 02:54 AM
Break it down



Template.Activate
Range("L6").Select