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


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

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.









Comments