Copying and Pasting after Autofilter Difficulties

Deutz

Board Regular
Joined
Nov 30, 2009
Messages
172
Office Version
  1. 365
Platform
  1. Windows
I have persistent and intermittent problems when trying to paste visible rows from one column on an autofiltered sheet into a column on another sheet with no autofilter or merged cells but in the same workbook.

I’m autofiltering a large dataset of about 50,000 rows a number of times with a variety of criteria and each run through I copy the results from the last column (minus the header row) to the other worksheet. When the filter returns a small number of rows it seems to copy ok but at other times the paste line in the code generates error (1004 the copy and paste area not being the same) and at other times not. Also, with the smaller autofilter results I get only the filtered rows, which is what I want, but with large results, of over 30,000 rows approx, I get all the rows in the sheet, not just the 30,000 filtered rows. I’ve tried copying just the visible cells but that also crashes intermittently and I don’t really know what the answer is after trying quite a number of possible solutions over a number of days.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I thought it might have something to do with the number of rows in the range to copy (first line of my code) and so I’ve tried putting in the number of rows returned by the filter and the total number of rows in worksheet as below but neither of these work consistently. I also thought of using the row number of the last filtered row but don’t know how to return it or if it will work? Any help would be appreciated.
<o:p></o:p>
shtData.Range(Cells(2, lngLastColOfData), Cells(65536, lngLastColOfData)).Select<o:p></o:p>
Selection.Copy<o:p></o:p>
shtResults.Activate<o:p></o:p>
lngLastRowInRes = GetLastRowInColumn(ResultsCol)<o:p></o:p>
shtResults.Cells(lngLastRowInRes + 2, ResultsCol).Value = ResHeader & SearchColName<o:p></o:p>
shtData.Activate<o:p></o:p>
ActiveSheet.Range(Cells(2, lngLastColOfData + 2), Cells(lngLastRowOfData, lngLastColOfData + 2)).Copy <o:p></o:p>
shtResults.Cells(lngLastRowInRes + 3, ResultsCol).Select<o:p></o:p>
shtResults.Cells(lngLastRowInRes + 3, ResultsCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o:p></o:p>
:=False, Transpose:=False<o:p></o:p>

<o:p></o:p>
Thanks
<o:p></o:p>
<?xml:namespace prefix = st1 /><st1:GivenName w:st="on">Michael</st1:GivenName>
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I have found when that happens that if you try it manually you will generally see what is going on.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top