General copy and filter worksheet

Page Content hide
1 Copying and filtering to columns on a target worksheet
2 Set up output columns
3 Write the one liner
4 Run it
5 Copy module code

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 worksheet, Make 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