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 linerYou 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
Run itWe get this
Copy module codeHere 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