Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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. -- Regards, Tom Ogilvy "Chuck Elsham" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 -- dave_ ----------------------------------------------------------------------- dave_d's Profile: http://www.excelforum.com/member.php...fo&userid=1460 View this thread: http://www.excelforum.com/showthread.php?threadid=26220 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 -- dave_ ----------------------------------------------------------------------- dave_d's Profile: http://www.excelforum.com/member.php...fo&userid=1460 View this thread: http://www.excelforum.com/showthread.php?threadid=26220 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
Hi Dave,
If you stop your code after the line Selection.copy can you complete the operation manually. Regards, Peter -----Original Message----- After another look at this problem - I have found that the paste operation has been completed successfully (and the data is still in the clipboard) then I get the error message. Just in case the problem was the next line of code - I inserted a msgbox line but the error occurred before it got to it. I will attach the code - it basically runs an autofilter on a sheet called 'Data' and then copies and pastes the filtered dataset to the '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 at this point Application.CutCopyMode = False Thanks Dave -- dave_d ---------------------------------------------------------- -------------- dave_d's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=14608 View this thread: http://www.excelforum.com/showthread...hreadid=262200 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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. "Peter T" wrote: Hi Dave, If you stop your code after the line Selection.copy can you complete the operation manually. Regards, Peter -----Original Message----- After another look at this problem - I have found that the paste operation has been completed successfully (and the data is still in the clipboard) then I get the error message. Just in case the problem was the next line of code - I inserted a msgbox line but the error occurred before it got to it. I will attach the code - it basically runs an autofilter on a sheet called 'Data' and then copies and pastes the filtered dataset to the '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 at this point Application.CutCopyMode = False Thanks Dave -- dave_d ---------------------------------------------------------- -------------- dave_d's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=14608 View this thread: http://www.excelforum.com/showthread...hreadid=262200 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 "paste method of worksheet class failed"
Sounds like you might have merged cells involved.
-- Regards, Tom Ogilvy "Jeff" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Select method of Range class failed" Error | Excel Discussion (Misc queries) | |||
"Copy Method of worksheet class failed" | Excel Programming | |||
VBA error 1004 "Select method of Range class failed" | Excel Programming | |||
Error 1004, "select method of range class failed" | Excel Programming | |||
Runtime Error "1004" Select Method of Range Class Failed | Excel Programming |