runtime error when pasting data

[tybaltlives]

Hi All: I am working between three worksheets. On the "Master Inputs" worksheet is a control button that calls userform6. userform6 has a combobox that allows the user to select a filter criteria for a table on "Saved data".

The idea is that the data will be filtered (on saved data) and then a few non-adjacent columns of the filtered data are copied and pasted to a range on the third worksheet called "Report data Selector".

Everything works but the paste portion. when that command runs I get:

runtime error 1004
"paste special method of range class failed"

I've done similar copying and pasting before using VB but never encountered this. Ive got excel 2010.

Can anyone help? seems like there is something simple that I am forgetting.

Thanks in advance,
R

Here is the code - all of it is initiated from selecting the filter criteria in the combobox on userform6:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br> <SPAN style="color:#00007F">Dim</SPAN> selldate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> headerrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> lastfilterrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> toprow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> coprange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#007F00">'</SPAN><br> <SPAN style="color:#007F00">' get filter criteria from combobox</SPAN><br> <SPAN style="color:#007F00">'</SPAN><br> selldate = userform6.ComboBox1.Value<br> <SPAN style="color:#007F00">'</SPAN><br> <SPAN style="color:#007F00">' filter worksheet data on "Saved Data"</SPAN><br> <SPAN style="color:#007F00">'</SPAN><br> ActiveSheet.Range("a1").CurrentRegion.AutoFilter field:=33, Criteria1:=selldate<br> <SPAN style="color:#007F00">'</SPAN><br> <SPAN style="color:#007F00">' Find range to copy filtered data</SPAN><br> <SPAN style="color:#007F00">'</SPAN><br>  <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>    headerrow = .AutoFilter.Range(1).Row<br>    lastfilterrow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row<br>    toprow = .Range(.Rows(headerrow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row<br>    <SPAN style="color:#00007F">If</SPAN> toprow = lastfilterrow + 1 <SPAN style="color:#00007F">Then</SPAN> toprow = 0<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  coprange = "a" + Trim(Str(toprow)) + ":b" + Trim(Str(lastfilterrow)) + "," + "i" + Trim(Str(toprow)) + ":i" + Trim(Str(lastfilterrow)) + "," + "q" + Trim(Str(toprow)) + ":r" + Trim(Str(lastfilterrow)) + "," + "af" + Trim(Str(toprow)) + ":af" + Trim(Str(lastfilterrow))<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' select range</SPAN><br><SPAN style="color:#007F00">'</SPAN><br>  Range(coprange).Select<br>  Selection.Copy<br>  <SPAN style="color:#007F00">'</SPAN><br>  <SPAN style="color:#007F00">' undo filters</SPAN><br>  <SPAN style="color:#007F00">'</SPAN><br>  ActiveSheet.ShowAllData<br>  <SPAN style="color:#007F00">'</SPAN><br>  <SPAN style="color:#007F00">' close user form</SPAN><br>  <SPAN style="color:#007F00">'</SPAN><br>   <br>  <SPAN style="color:#007F00">'</SPAN><br>  <SPAN style="color:#007F00">' move over to "Report Data Selection and paste data</SPAN><br>  <SPAN style="color:#007F00">'</SPAN><br>    Sheets("Report data selector").Select<br>    Range("A19").Select<br>    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br>        :=False, Transpose:=False<br> Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[tybaltlives]

OK. While waiting for a response I continued to work on the problem (as well as help out other people asking different questions). I finally got things to work.

Deleting this command: ActiveSheet.ShowAllData

Was the key.

I still want to un filter the data once I'm done copying it. But I guess I will have to do that after the paste.

Thanks all for much great help both in the past and in advance for the future!

-R