General copy and filter worksheet

Copying and filtering to columns on a target worksheet

In Make a filtered copy of a worksheet I covered how to use cDataSet to do a one-liner copy and filter of one worksheet to another.  Now lets generalize that so that you can name the columns in the target worksheet instead of having to build that into code. 

Let's take this sheet, and lets say that we wanted to copy the rows where seller.power_seller_status was platinum. There are plenty of ways to do this of course, many of them demonstrated on this site such as Execute SQL from Excel , Make a copy of some of the columns in a worksheetMake a filtered copy of a worksheet , How to use cDataSet and so on, but this method will allow you to decide which columns to copy just by naming the columns on the target worksheet.



Set up output columns

The first step is to add the column names you want copied in some worksheet.

Write the one liner

You could of course create a form to provide the variable data, or ask the user, but for now lets make it a one liner.

Public Sub straightCopyLibre()
    straightCopyAll "mercadoLibre", "targetWorksheet", "seller.power_seller_status", "platinum"
End Sub

That's all

Run it

we get this.


Copy module code

Here is the code of the general copy module

Public Sub straightCopyAll(fromSheet As String, _
                    toSheet As String, _
                    Optional filterCol As String = vbNullString, _
                    Optional filterValue As Variant)
    Dim ds As New cDataSet, ds2 As New cDataSet, hc As cCell
    Dim a() As String
    With ds2
        .populateData wholeSheet(toSheet), , , , , , True
        ReDim a(1 To .columns.count)
        For Each hc In .headings
            a(hc.column) = hc.toString
        Next hc
    End With
    
    ds.populateData(wholeSheet(fromSheet), , , , , , True).bigCommit _
            ds2.headingRow.Where, True, a, filterCol, filterValue

End Sub

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