Paste method faile (activesheet.paste)

M

MS Excel

Hi,

While using Activesheet.Paste in a newly created book through workbook.add
function, I got following message
"Paste method of workbook class failed." To my amazement, the same was
working perfectly in MS EXCEL XP
but when upgraded to Excel 2003 its creating problem..

Any idea.?
 
D

Dave Peterson

Maybe adding...

msgbox application.cutcopymode

before you do the paste would tell you if there's anything copied that can be
pasted.

If there isn't anything to paste, sometimes just rearranging the order can help.

dim rngtocopy as range
dim newwks as worksheet

with worksheets("sheet99")
set rngtocopy = .range("a1:b99")
end with

set newwks = workbooks.add.worksheets(1)

rngtocopy.copy _
destination:=newwks.range("a1")
 
T

Tom Ogilvy

To my amazement, the same was
working perfectly in MS EXCEL XP
but when upgraded to Excel 2003 its creating problem..
Click to expand...

Guess this isn't completely factual.
 
D

Dave Peterson

Did the OP post something somewhere else?

I would have guessed that it wasn't just the change of versions that did
this--but it could have been a something that occurred at the same time that may
have killed the cutcopymode?

Maybe an event macro was added that kills it--who knows?
 
M

MS Excel

I m using selection.copy method...
one more thing... when typing activesheet. i m not getting any drop down
list (online help)
 
M

MS Excel

Below is the code that i want to run. but.. it fails on last line..

Sub Testing()

Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add Template:="Workbook"
Range("B2").Select
Application.CutCopyMode = True
ActiveSheet.Paste

End Sub

MS Excel said:
I m using selection.copy method...
one more thing... when typing activesheet. i m not getting any drop down
list (online help)
Click to expand...
 
D

Dave Peterson

Try changing the order of your code:

Option Explicit
Sub Testing()

Dim myRng As Range
Dim newWks As Worksheet
Dim CurWks As Worksheet

Set CurWks = ActiveSheet

With CurWks
Set myRng = .Range("a5", .Range("a5").End(xlToRight))
Set myRng = .Range(myRng, myRng.End(xlDown))
End With

Set newWks = Workbooks.Add.Worksheets(1)

myRng.Copy _
Destination:=newWks.Range("b2")

Application.CutCopyMode = False

End Sub



MS said:
Below is the code that i want to run. but.. it fails on last line..

Sub Testing()

Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add Template:="Workbook"
Range("B2").Select
Application.CutCopyMode = True
ActiveSheet.Paste

End Sub
Click to expand...
 
D

Dave Peterson

If you use a variable for the activesheet (and declare it as a worksheet),
you'll see VBA's intellisense.

dim curwks as worksheet
set curwks = activesheet
with curwks
.....

MS said:
I m using selection.copy method...
one more thing... when typing activesheet. i m not getting any drop down
list (online help)
Click to expand...
 
M

MS Excel

Sorry for bothering you again... but now its showing another error, "Copy
mehtod of Range class failed".

however, the data is properly pasted in the next sheet and after that error
appeared.

Thanks for your support.
I will check & will revert with outcomes.

Syed
Click to expand...
 
D

Dave Peterson

The code I posted worked ok for me and I don't see anything in that code that
would cause trouble.

You may want to post the code you're using.

MS said:
Sorry for bothering you again... but now its showing another error, "Copy
mehtod of Range class failed".

however, the data is properly pasted in the next sheet and after that error
appeared.
Click to expand...
 
M

MS Excel

I tried your code in new workbook created under 2003. its working fine.

Thanks for your support,


Dave Peterson said:
The code I posted worked ok for me and I don't see anything in that code
that
would cause trouble.

You may want to post the code you're using.
Click to expand...
 
Top