Excel VBA method Cells of object _global failed

Two questions:

1) Is Sheets(1) not a worksheet?

2) Does this work:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______


spoefi wrote:

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 with
'XXXX') Why?

Important maybe: the code is in Outlook


Dim Sheettempl As Worksheet
Set Sheettempl =
appExcel1.Workbooks.Open("c:\projects\macros\templ ate.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 destRange


---
Message posted from http://www.ExcelForum.com/

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

The line above contains an unqualified reference to the Cells property.
If you look up XL VBA help, you will find that XL treats such an
unqualified reference as applying to the ActiveSheet. If the active
sheet is not a worksheet or if it is not the same as Sheetbasis, you
will get an error.

One way to do this would be:

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

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , spoefi
says...
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 with
'XXXX') Why?

Important maybe: the code is in Outlook


Dim Sheettempl As Worksheet
Set Sheettempl =
appExcel1.Workbooks.Open("c:\projects\macros\templ ate.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 destRange


---
Message posted from http://www.ExcelForum.com/