XL2003 "paste method of worksheet class failed"

[Chuck Elsham]

Hello folks,

Can someone help me with what I expect is a really simple problem -

I have a small piece of code in it's own module.

The code is to be called manually once the user has copied tabular
data from another app.

The first part of the procedure is to add a worksheet & paste the
clipboard contents.

For some reason the paste method now seems to fail with "paste method
of worksheet class failed".

I checked on Google Groups and found 89 threads relating to this
problem. There doesn't seem to be a specific error people are
committing. Has this been acknowledged as a bug by Microsoft?

The most helpful response was from an MVP, Nick Hodge who mentioned
that Excel - he responds to a similar question:-

"I suspect XL has lost what it wanted to paste. You don't show us the
code for copying, but if you do much after you have copied the chances
are XL will lose it's clipboard. (It doesn't work like most other
apps). "

Really? Excel loses the clipboard contents in a random way?

===================================


Here is the part of the sub that fails:-

Sub getcolumns(colControlDate As Integer, colYear As Integer,
intColumnCount As Integer _
, Optional colCurrency As Integer, Optional
colPaid As Integer _
, Optional colOS As Integer)

Dim headings As Range
Dim tmpString As String, i As Integer


' create a new sheet so we don't overwrite existing data
Application.Worksheets.Add


'paste clipboard data into new sheet
ActiveCell.PasteSpecial (xlPasteValues)


'select first cell of data & count number of columns in the region
Range("a1").Select
intColumnCount = ActiveCell.CurrentRegion.Columns.Count

' quit if clipboard did not produce at least 3 cols
If intColumnCount < 3 Then
MsgBox ("No columns of data found in Windows Clipboard")
Exit Sub
End If

end sub

========================================

Thanks in advance for any help,
Rob

 

[Tom Ogilvy]

Does it consistently fail?

If so, then it doesn't appear to be random

Microsoft doesn't see it as a bug, but by design.

 

[keepITcool]

Rob,

For debugging you could check the clipboard to see if there's
some pasteable data on it.

following checks for a range object:

Function RangeOnCB() as Boolean
With Application
If Not IsError(.Match(xlClipboardFormatBIFF4, _
.ClipboardFormats, 0)) Then RangeOnCB = True
End With
End Function

adapt to the proper clipboardconstant of the data (see help on
clipboaardformats) that is put on the clipboard by your 'other' app, and
check the clipboard the moment you return from it. if the function
returns False you must ask the user to repeat the copy process.




keepITcool

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

 

[dave_d]

I have just joined the forums to see if I could get a solution to thi
problem

There is an Excel 2000 sheet & Macro that I have been using for
while, but when running in Excel 2003 I get the same 'paste method o
worksheet failed'

I have ensured that the sheet is selected & activated before doing th
paste

Oddly - I have just recorded a macro to do the same thing and I a
puzzled...

Its shows the select and the copy - but there is no code in there fo
the paste operation???

Set up a new sheet with a little data, done the same thing and ther
*is* a paste line in the code :confused

 

[Chuck Elsham]

Thanks to KeepITCool for the function - that will work as a smooth
workaround.

Tom, what did you mean by :-

"Microsoft doesn't see it as a bug, but by design."

I'm stuck on "seeing something by design".

Do you know when the clipboard is designed to be cleared - is this
documented? I've got used to this when using XL 'interactively' -
I've reached the point where I instinctively know whether the
clipboard is empty.

What I'm looking for is some description of which types of methods
in VBA will result in the windows clipboard being emptied.

And is there info on MSDN about the 'Office Clipboard' that pertains
to VBA in excel?


From the number of puzzled posts to the newsgroups it seems something
has changed in XL 2003, which results in this error where VBA
previously did not (in earlier versions)

Thanks again for taking the time to answer.


Rob

 

[dave_d]

After another look at this problem - I have found that the past
operation has been completed successfully (and the data is still in th
clipboard) then I get the error message.

Just in case the problem was the next line of code - I inserted
msgbox line but the error occurred before it got to it.

I will attach the code - it basically runs an autofilter on a shee
called 'Data' and then copies and pastes the filtered dataset to th
'Air' sheet.

Sheets("Data").Select
Selection.AutoFilter Field:=16, Criteria1:="A"

ActiveSheet.Range("A1").Select
ActiveCell.CurrentRegion.Rows.Select
Selection.copy

Sheets("Air").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste '<---- Fails a
this point

Application.CutCopyMode = False


Thanks

Dav

 

[Peter T]

Hi Dave,

If you stop your code after the line
Selection.copy
can you complete the operation manually.

Regards,
Peter

 

[Guest]

It's good to know I'm not the only one with this very same problem. My code
worked in excel 2002. The same solution with a manual code stop works for me
too. Let's hope microsoft sorts this out soon or I will have to reinstall
the older version.

 

[Guest]

I need a fix for this same problem the following simple recorded macro will
develope the following error: "paste method of worksheet class failed"
if i comment out the Application.CutCopyMode = False line I get a different
error:
Method 'Paste' of object '_worksheet' failed.
then excel is messed up I can step through the macro but it does not execute
any of the lines of code.
I am using office 2002 or '10'

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/12/2005
'
Rows("4:4").Select
Selection.Copy
Sheets("vbaHrs").Select
Rows("4:4").Select
Application.CutCopyMode = False
ActiveSheet.Paste
End Sub

 

[Tom Ogilvy]

You can try this

Sub copyrow4()
Activesheet.Rows(4).Copy Destination:= _
worksheets("vbaHrs").Rows(4)
End Sub

 

[Guest]

Thanks for the reply. I tried your idea and it still failed I thought it
might be a formula cell causing the problem so i tried paste sepcial values
only and that worked. but I need the formulas and format so I tried pasteall
and that caused the same errors so then I just made three passes (ugly) one
for each type ie value then formula then format and guess what It Worked. Not
very efficient but as long as it works.

Thanks

 

[Tom Ogilvy]

Sounds like you might have merged cells involved.