Paste method faile (activesheet.paste)

[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.?

 

[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")

 

[Tom Ogilvy]

To my amazement, the same was

working perfectly in MS EXCEL XP
but when upgraded to Excel 2003 its creating problem..

Guess this isn't completely factual.

 

[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?

 

[MS Excel]

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

 

[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

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

 

[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

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

 

[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
.....

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

 

[syedobaidullah]

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

Syed

 

[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

 

[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.

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.

 

[MS Excel]

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

Thanks for your support,

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.