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