In General copy and filter worksheet I showed how to copy a sheet using filters passed as arguments to the cDataSet class. Here is another way to do it, this time respecting any filters that are applied on the source worksheet. This one liner copies only the data that is visible
Public Sub copyAndRespectFilter() Dim ds2 As New cDataSet ds2 _ .populateData(wholeSheet("mercadoLibre"), , , , , , True, , , , True) _ .bigCommit wholeSheet("messaround"), True End Sub
WalkthroughHere’s the call stack to populateData. The last argument, bRecordFilter, instructs cDataSet to keep track of the autofilter status for each row as it loads the data.
Public Function populateData(Optional rstart As Range = Nothing, Optional keepFresh As Boolean = False, Optional sn As String = vbNullString, _ Optional blab As Boolean = False, Optional blockstarts As Variant, _ Optional ps As cDataSets, _ Optional bLikely As Boolean = False, _ Optional sKey As String = vbNullString, _ Optional maxDataRows As Long = 0, _ Optional stopAtFirstEmptyRow As Boolean = True, _ Optional brecordFilter As Boolean = False) As cDataSet
That’s all there is. bigCommit will only commit those rows that are not hidden by any operational autoFilter. Any other bigCommit parameters such as Make a filtered copy of a worksheet and Make a copy of some of the columns in a worksheet can be applied in addition to this if necessary.