How to Merge 2 Filtered Tables into a 3rd Table

[R4ndm]

Hi,

I'm trying to do a merge of 2 filtered tables into a 3rd one.

Here is a simple example of what I'm trying to do. I have 3 sheets (Husband_Expenses, Wife_Expenses, Family_House_Expenses) and each sheet has a table (Husband_Expenses_T, Wife_Expenses_T, Family_House_Expenses_T) that contains rows of expense data.

Both the Husband_Expenses_T and Wife_Expenses_T, have an Expense_Type column that I can use to filter the expenses to show only the ones I want to see (for example, House, Car, Food, etc.)

I've been able to use the following code to merge both Husband_Expenses_T and Wife_Expenses_T into a 3rd table that contains all records so essentially a Family_Expenses_T. But I would like to filter both these tables first so that I only copy rows that have Expense_Type with a value of "House" into Family_House_Expenses_T table on the Family_House_Expenses sheet.

Sub Populate_Family_House_Expenses_T()
Dim lastRow As Variant
Dim mySheets

mySheets = Array("Husband_Expenses", "Wife_Expenses")

Family_House_Expenses_T 'This function empties the table in case it currently contains data.

For i = 0 To UBound(mySheets)
Set lastRow = Sheets("Family_House_Expenses").ListObjects("Family_House_Expenses_T").ListRows.Add
Sheets(mySheets(i)).ListObjects(mySheets(i) & "_T").DataBodyRange.Copy
lastRow.Range.PasteSpecial xlPasteValues
Next i
End Sub

Can someone point me in the right direction?
Thanks.

 

[Fazza]

hi,

Preferably set up the data in one table instead of three: with a new field for Husband, Wife, Family-House. Then all sorts of things are MUCH easier.

If maintaining three tables, they can be combined using a query - this requires no formulas & no VBA. Can be set up via menu ALT-D-D-N and follow the wizard. There is an option via the wizard to filter, such as for "House", or there are other approaches. One way that is a bit limited but does what you want is simply hard code the SQL. So for the data given, it would be like below.

HTH. Regards, Fazza

SELECT *
FROM (
SELECT * FROM Husband_Expenses_T
UNION ALL
SELECT * FROM Wife_Expenses_T
UNION ALL
SELECT * FROM Family_House_Expenses_T)
WHERE Expense_Type = 'House'

 

[R4ndm]

Thanks for the suggestions. Using the SQL is an interesting way to do it.
As for having a field for husband/wife/family-house, I can't because each sheet is populated by downloading the finances directly from either the husband's or wife's online banking, so it's easier to just dump into a sheet.

I found a way to achieve what I wanted by using the AutoFilter directly in my loop:

For i = 0 To UBound(mySheets)
Set lastRow = Sheets("Family_House_Expenses").ListObjects("Family_House_Expenses_T").ListRows.Add
Sheets(mySheets(i)).ListObjects(mySheets(i) & "_T").ShowAutoFilter = True
Sheets(mySheets(i)).ListObjects(mySheets(i) & "_T").AutoFilter.ShowAllData
Sheets(mySheets(i)).ListObjects(mySheets(i) & "_T").DataBodyRange.AutoFilter Field:= 4, Criteria1:="=House"
Sheets(mySheets(i)).ListObjects(mySheets(i) & "_T").DataBodyRange.Copy
lastRow.Range.PasteSpecial xlPasteValues
Next i

This worked and did exactly what I was trying to.

 

[Fazza]

Great. Thanks for the update