Copying and Pasting after Autofilter Difficulties

[Deutz]

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-comfficeffice" /><o></o>
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></o>
shtData.Range(Cells(2, lngLastColOfData), Cells(65536, lngLastColOfData)).Select<o></o>
Selection.Copy<o></o>
shtResults.Activate<o></o>
lngLastRowInRes = GetLastRowInColumn(ResultsCol)<o></o>
shtResults.Cells(lngLastRowInRes + 2, ResultsCol).Value = ResHeader & SearchColName<o></o>
shtData.Activate<o></o>
ActiveSheet.Range(Cells(2, lngLastColOfData + 2), Cells(lngLastRowOfData, lngLastColOfData + 2)).Copy <o></o>
shtResults.Cells(lngLastRowInRes + 3, ResultsCol).Select<o></o>
shtResults.Cells(lngLastRowInRes + 3, ResultsCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>

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

 

[texasalynn]

I have found when that happens that if you try it manually you will generally see what is going on.