Greetings and reverence to all gurus ! (with a special notice for Stephen
Bullen ;-))


Nothing more simple than copy a range ? This code works normally in VBA:

Sheets(1).Select
ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select
Selection.Copy
Sheets(2).Select
Cells(2, 1).Select
Sheets(2).Paste

In fact, this code is generated by the macro recorder.

The following is the translation in my VB dll :

Set mySheet = XLS.Sheets(1) 'XLS represents my Workbook
mySheet.Activate 'this line is correctly
implemented
With mySheet
.Range(Cells(l1, 1), Cells(l2, 2)).copy

This last line fails. It is Range that fails :
set r = .Range(Cells(l1, 1), Cells(l2, 2)) also fails (method '~'
failed)

Anyone know why ? And how to get around this ?

PM,

I expect that you need to be more specific with your cells object, since
your VBA depends on defaults. Cells defaults to the cells collection of the
activesheet, but more specific would be:
VBA:
Old: ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select

New: ActiveSheet.Range(ActiveSheet.Cells(l1, 1), ActiveSheet.Cells(l2,
2)).Select

So try:

With mySheet
.Range(.Cells(l1, 1), .Cells(l2, 2)).copy

HTH,
Bernie
MS Excel MVP

"PM" wrote in message
...
Greetings and reverence to all gurus ! (with a special notice for Stephen
Bullen ;-))


Nothing more simple than copy a range ? This code works normally in VBA:

Sheets(1).Select
ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select
Selection.Copy
Sheets(2).Select
Cells(2, 1).Select
Sheets(2).Paste

In fact, this code is generated by the macro recorder.

The following is the translation in my VB dll :

Set mySheet = XLS.Sheets(1) 'XLS represents my Workbook
mySheet.Activate 'this line is correctly
implemented
With mySheet
.Range(Cells(l1, 1), Cells(l2, 2)).copy

This last line fails. It is Range that fails :
set r = .Range(Cells(l1, 1), Cells(l2, 2)) also fails (method '~'
failed)

Anyone know why ? And how to get around this ?

Try

With mySheet
.Range(.Cells(l1, 1), .Cells(l2, 2)).copy


--

HTH

RP
(remove nothere from the email address if mailing direct)


"PM" wrote in message
...
Greetings and reverence to all gurus ! (with a special notice for Stephen
Bullen ;-))


Nothing more simple than copy a range ? This code works normally in VBA:

Sheets(1).Select
ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select
Selection.Copy
Sheets(2).Select
Cells(2, 1).Select
Sheets(2).Paste

In fact, this code is generated by the macro recorder.

The following is the translation in my VB dll :

Set mySheet = XLS.Sheets(1) 'XLS represents my Workbook
mySheet.Activate 'this line is correctly
implemented
With mySheet
.Range(Cells(l1, 1), Cells(l2, 2)).copy

This last line fails. It is Range that fails :
set r = .Range(Cells(l1, 1), Cells(l2, 2)) also fails (method '~'
failed)

Anyone know why ? And how to get around this ?

YES !!

Your perfectly right. I am still infused with VBA style writing.
Thanks a million ! I just couldn't see it before you said it !

Pat

"Bernie Deitrick" <deitbe @ consumer dot org a écrit dans le message de
news: ...
PM,

I expect that you need to be more specific with your cells object, since
your VBA depends on defaults. Cells defaults to the cells collection of
the
activesheet, but more specific would be:
VBA:
Old: ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select

New: ActiveSheet.Range(ActiveSheet.Cells(l1, 1), ActiveSheet.Cells(l2,
2)).Select

So try:

With mySheet
.Range(.Cells(l1, 1), .Cells(l2, 2)).copy

HTH,
Bernie
MS Excel MVP

"PM" wrote in message
...
Greetings and reverence to all gurus ! (with a special notice for Stephen
Bullen ;-))


Nothing more simple than copy a range ? This code works normally in VBA:

Sheets(1).Select
ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select
Selection.Copy
Sheets(2).Select
Cells(2, 1).Select
Sheets(2).Paste

In fact, this code is generated by the macro recorder.

The following is the translation in my VB dll :

Set mySheet = XLS.Sheets(1) 'XLS represents my Workbook
mySheet.Activate 'this line is correctly
implemented
With mySheet
.Range(Cells(l1, 1), Cells(l2, 2)).copy

This last line fails. It is Range that fails :
set r = .Range(Cells(l1, 1), Cells(l2, 2)) also fails (method '~'
failed)

Anyone know why ? And how to get around this ?

.Range(Cells(l1, 1), Cells(l2, 2)).copy

note that Cells refers to the activesheet! as it is not qualified,
try like :

.Range(.Cells(l1, 1), .Cells(l2, 2)).copy


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"PM" wrote :

Greetings and reverence to all gurus ! (with a special notice for
Stephen Bullen ;-))


Nothing more simple than copy a range ? This code works normally in
VBA:

Sheets(1).Select
ActiveSheet.Range(Cells(l1, 1), Cells(l2, 2)).Select
Selection.Copy
Sheets(2).Select
Cells(2, 1).Select
Sheets(2).Paste

In fact, this code is generated by the macro recorder.

The following is the translation in my VB dll :

Set mySheet = XLS.Sheets(1) 'XLS represents my Workbook
mySheet.Activate 'this line is
correctly implemented
With mySheet
.Range(Cells(l1, 1), Cells(l2, 2)).copy

This last line fails. It is Range that fails :
set r = .Range(Cells(l1, 1), Cells(l2, 2)) also fails (method '~'
failed)

Anyone know why ? And how to get around this ?