method Cells of object _global failed

[spoefi]

Excel VBA method Cells of object _global failed
run time error 1004
Method 'Cells' of object '_global' failed

I try to copy a range from one sheet to another in another workbook.
I get an error on the part where I define my source range (marked wit
'XXXX') Why?

Important maybe: the code is in Outlook and 'calls' excel


Dim Sheettempl As Worksheet
Set Sheettempl
appExcel1.Workbooks.Open("c:\projects\macros\template.xls").Sheets(1)
Dim Sheetbasis As Worksheet
Set Sheetbasis = appExcel1.Workbooks.Open("c:\projects\macros\test"
intTeller & ".xls").Sheets(1)


Dim srceRange As Range
Dim destRange As Range

Set srceRange = Sheetbasis.Range(Cells(2, 2), Cells(8, 2)) 'XXXX
Set destRange = Sheettempl.Cells(2, 2)

srceRange.Copy destRang

 

[Stephen Bullen]

Hi Spoefi,

Set srceRange = Sheetbasis.Range(Cells(2, 2), Cells(8, 2)) 'XXXX

You haven't fully-qualified the Cells calls:

Set srceRange = Sheetbasis.Range(Sheetbasis.Cells(2, 2), _
Sheetbasis.Cells(8, 2))

Or:

With Sheetbasis
Set srceRange = .Range(.Cells(2, 2), .Cells(8, 2))
End With


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[spoefi]

Stephen, thank you very much. I've spent an awful lot of time lookin
for a solution.

spoef