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
.Value = .List(0)
With New cDataSet
.populateData wholeSheet(cbxWsIn.Value), , , , , , True, , 1
If Not .Where Is Nothing Then
For Each hc In .Headings
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, True
Private Sub cbunSelect_Click()
moveList lbxSelected, lbxAvailable, False
Private Sub moveList(lbFrom As MSForms.ListBox, lbTo As MSForms.ListBox, bSelecting As Boolean)
Dim i As Long
If .ListIndex = -1 Then Exit Sub
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then
End 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
For i = 0 To .ListCount - 1
ReDim Preserve a(1 To i + 1)
a(UBound(a)) = .List(i)
If UBound(a) >= LBound(a) Then
If cbxWsOut.Value = cNotSelectedWsOut Then
'create a sheet
Set ws = Sheets.add
Set ws = Sheets(cbxWsOut.Value)
With New cDataSet
' get input sheet
.populateData wholeSheet(cbxWsIn.Value), , , , , , True
If cbxFilterField.Value = cNotSelectedFilter Then
sf = vbNullString
sf = cbxFilterField.Value
.bigCommit ws.Cells(1, 1), True, a, sf, tbFilterMatch.Value, chbApproximate.Value
MsgBox ("nothing to do")
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.