0

I am attempting to understand why a certain bit of code will not work in Excel 2010.

I have a file where cells A1:K200 are part of a table (the Format as Table option form the Home tab), but rows 2-13 are hidden for use with data validation combo boxes. Header row and row banding options are enabled. What I want to do is copy (via the user pressing Ctrl-C) and paste (via user pressing Ctrl-V) data from another workbook and then have a macro function in Worksheet_Change that will transpose the data (based on the value of a checkbox on the worksheet) and paste only the values. Columns A-H will be filled out with other data.

I have disabled events and screen updating, set up a range for cells I14:I200 as StdCost for an intersect function and then have the following code to trap changes to the above range. optTranspose is a checkbox I have placed on the sheet and Target is the variable set up by Excel as part of the Worksheet_Change function. 

If Not Application.Intersect(Target, StdCost) Is Nothing Then
    If Application.CutCopyMode = xlCopy Then
        Application.Undo
        .Range("I" & Target.Row).PasteSpecial Paste:=xlPasteValues, Transpose:=optTranspose
    End If
End If

The above code will work, but based on my testing it will only work with a maximum of 4 values being pasted in OR it will work if I paste starting on row 15 or below. However, if I paste 5+ values I get the error from the subject

PasteSpecial method of range class failed

From my testing, what appears to happen is that before the undo operation kicks in, Excel automatically adds Column1 and Column2 to columns L and M, which then causes Target to take on "Column1" and "Column2" and thus ends up causing the error when I try and run it on the actual values I want to paste. If I convert the table to a normal range, everything appears to operate as expected.

I want to understand why "Column1" and "Column2" are being inserted by Excel, why it's only at 5+ cells from another spreadsheet that it becomes and issue and how I might be able to account for this in my code.

Aerogems
  • 25
  • 8

2 Answers2

0

I want to understand why "Column1" and "Column2" are being inserted by Excel,

This happens when you paste data adjacent to an existing ListObject table.

The change event occurs (i.e., you "Paste" the data), the worksheet changes, and then, the Worksheet_Change event fires.

The error seems to happen because Application.Undo is undoing the Paste operation and further affecting the Application.CutCopyMode = False, so on the next line when you attempt to use the PasteSpecial method, there simply is nothing that can be pasted, because the Undo has cleared it.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • As this is just a small segment of a couple hundred lines of code, I realize I neglected to mention that I do disable both events and screen updating. I have a function to turn both of those on and off, but just to test I commented it out, added `Application.EnableEvents = False` at the top of my code and get the exact same error. – Aerogems Feb 24 '15 at 20:19
  • You may be better off adding a button to perform the paste operation entirely via VBA, rather than trying to capture it as an event. – David Zemens Feb 24 '15 at 20:31
  • That explains what is happening and why, now for my own edification I want to know if there's some way I can override this behavior in the event it may become useful later. At this time, I was using the table primarily as a convenient means of providing shading to alternating rows, so I can live without it if it is going to be an issue. – Aerogems Feb 24 '15 at 20:49
  • Which specific behavior are you trying to override? The automatic insertion of the table column headers, or the `Undo` clearing out the clipboard? – David Zemens Feb 24 '15 at 21:06
  • There will not be an easy way to do the former (i.e., there is no built in method to do this although you could develop some complicated logic to test for "adjacency" with the existing ListObject table...) and the latter is probably not possible at all. Do remember to mark answers as "Accepted" if they've answered the question/solved the problem. Cheers. – David Zemens Feb 24 '15 at 21:08
  • It would be the former, overriding the automatic creation of table headers. Guess I will have to dump the table. – Aerogems Feb 24 '15 at 22:08
  • Yeah, I think the table headers will automatically happen whenever you paste, insert, or enter data adjacent to an existing table row/column. You can "undo" this like you tried, but that breaks the "paste", it would be possible to make a clunky workaround, where you temporarily add a hidden worksheet, paste the data there, then paste it in the destination sheet, then do the undo, then try to re-paste from the hidden sheet accounting for adjacency, etc., then delete the hidden sheet. But that may be overkill :) – David Zemens Feb 24 '15 at 22:11
-1

Pastespecial seems to be very sensitive to the parameters it's fed.

I was experiencing exactly the same error message from code that had worked before migrating to Windows 10. I found this combination of parameters worked in my case:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Can't claim that I found that combo of parameters- it came from yactici's self answer

Kudos to yactici for returning to his initial problem despite having a workaround and directly resolving the problem with pastespecial.

Community
  • 1
  • 1
Michael
  • 726
  • 7
  • 18