Referencing Other Open Workbooks.

[paddysheeran]

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.

[vba]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[/vba]

thanks.

paddy.

[Bob Phillips]

[vba]

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
[/vba]

[paddysheeran]

Hi this works until the line:

[VBA]Template.Range("L6").Select[/VBA]

I then get the error message:

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

[Bob Phillips]

Break it down

[vba]

Template.Activate
Range("L6").Select
[/vba]