Users often apply different filters to loaded data, hide and unhide columns, sort in various ways, etc.
The SaveToDB add-in may help to save such user views and even share them with colleagues.
Let's remove all WHERE filters and click the Save Table View button in the Table Views group:
Type All Payments and click Save.
We see the name of the current view, All Payments, in the Table View field.
Type >0 in cell E2.
The add-in applies the filter to the Sum column.
This is a reason why it is better to insert tables at cell B3.
Users may use row 2 (as a row over the table) as auto-filters. Also, they may place formulas in row 1.
Let's continue and save the view as Incomes (click the Save Table View button again):
Type <0 in cell E2.
The add-in applies the new filter to the Sum column.
Save the view as Expenses.
Remove the filter in cell E2 and apply the Incomes view:
As we may expect, the add-in applies the saved filter to the Sum column.
I am sure, your users will be happy, and you will have fewer requests for new small database views.
The following code creates table views described in the chapter:
' Creating table views, Chapter 9 Sub Chapter09_1_CreateTableViews() Dim addIn As Object Set addIn = GetAddInAndCheck() If addIn Is Nothing Then Exit Sub Dim ws As Worksheet Set ws = GetWorksheet(ActiveWorkbook, "Payments") If ws Is Nothing Then MsgBox "Worksheet Payments not exists" Exit Sub End If ws.Select Dim lo As ListObject Set lo = GetFirstListObject(ws) If lo Is Nothing Then MsgBox "Worksheet Payments does not contain a table" Exit Sub End If Dim cell As Range Set cell = ws.Range("E2") Call addIn.SaveTableView(lo, "All Payments") cell.Value = ">0" Call addIn.SaveTableView(lo, "Incomes") cell.Value = "<0" Call addIn.SaveTableView(lo, "Expenses") cell.ClearContents Call addIn.ApplyTableView(lo, "Incomes") End Sub
Mainly, you will use the SaveTableView and ApplyTableView methods.
Also, you may use the auto-filter row over the tables to filter data in an easy way.