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" wrote:

You can try this

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

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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


"Chuck Elsham" wrote:

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