What can you learn here?

  • Select worksheets to copy
  • Choose selected columns
  • cDataSets filtering capability

Copying partial tables between sheets

Get the cDataset.xlm from here

By now you are probably familiar with the cDataSets classes. Here is a straightforward implementation that provides a form you can use to select fields in a table to be copied to other sheets. It should demonstrate how versatile applications can be built in a very short time.

The control form.

This is the filter form in cDataSet.slsm, and allow you to

  • Select which worksheet needs to be copied
  • Create a new sheet or copy data to an existing one.
  • Select fields to be copied from the list of fields on that sheet
  • Filter for particular values in a field to qualify or disqualify the table row from being copied over

Creating the list of fields on a worksheet

When a worksheet is selected, we clear the listboxes and filter fields, which are dependent on which worksheet is selected,  and we create a new dataset based on the newly selected worksheet.  Finding which column headings are present in the sheet then becomes a simple iteration through the .Headings property.  By default these are all assumed to be fields to copy, so they are added to the selected listbox.  The .populateData method is restricted to only read 1 row here, since we are only interested in the headings at this point and don’t need to waste time reading the data. Private Sub cbxWsIn_Click()    Dim hc As cCell        clearxb lbxAvailable    clearxb lbxSelected    clearxb cbxFilterField        With cbxFilterField        .AddItem cNotSelectedFilter        .Value = .List(0)    End With        With New cDataSet        .populateData wholeSheet(cbxWsIn.Value), , , , , , True, , 1        If Not .Where Is Nothing Then            For Each hc In .Headings                lbxSelected.AddItem hc.toString                cbxFilterField.AddItem hc.toString            Next hc        End If    End WithEnd Sub

Selecting and deselecting the fields to be copied

By default, all fields are in the right hand list box meaning that they will be copied from the old sheet to the new sheet. The < and > command buttons allow this behavior to be modified by moving fields between the ‘available’ and ‘selected’ listboxes. This is how to move selections between two listboxes that have been set to allow multiple selections. Private Sub cbSelect_Click()    moveList lbxAvailable, lbxSelected, TrueEnd SubPrivate Sub cbunSelect_Click()    moveList lbxSelected, lbxAvailable, FalseEnd SubPrivate Sub moveList(lbFrom As MSForms.ListBox, lbTo As MSForms.ListBox, bSelecting As Boolean)        Dim i As Long        With lbFrom            If .ListIndex = -1 Then Exit Sub            For i = .ListCount - 1 To 0 Step -1                If .Selected(i) Then                    lbTo.AddItem .List(i)                    .RemoveItem i                End If            Next i        End WithEnd Sub Finally this is what happens onSubmit.   This time .populate data reads all the data in the input worksheet. The .bigCommit method has all the capabilities to copy from a data set to some other set of worksheet cells, and also knows how to apply the selected filter to only copy over qualifying records. Private Sub cbSubmit_Click()    Dim ws As Worksheet, ds As cDataSet, a() As String, i As Long, sf As String    With lbxSelected        For i = 0 To .ListCount - 1            ReDim Preserve a(1 To i + 1)            a(UBound(a)) = .List(i)        Next i    End With        If UBound(a) >= LBound(a) Then        If cbxWsOut.Value = cNotSelectedWsOut Then            'create a sheet            Set ws = Sheets.add        Else            Set ws = Sheets(cbxWsOut.Value)            ws.Activate        End If            With New cDataSet            ' get input sheet            .populateData wholeSheet(cbxWsIn.Value), , , , , , True            If cbxFilterField.Value = cNotSelectedFilter Then                sf = vbNullString            Else                sf = cbxFilterField.Value            End If                        .bigCommit ws.Cells(1, 1), True, a, sf, tbFilterMatch.Value, chbApproximate.Value                    End With    Else        MsgBox ("nothing to do")    End IfEnd Sub

Summary

The cDataSet class has many capabilities that enable the manipulation of spreadsheet data with minimal effort. As usual your comments , feedback and questions are welcome on our forum.