Make a copy of a sheet and respect autofilter

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

Page Content hide
1 Walkthrough
2 Summary

Walkthrough

Here’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.

Summary

Take a look at One Liners for more tips like this. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. You can this example in the cDataSet.xlsm from Downloads