Selected Answer
Hello RUSSO and welcome to the forum.
The resultant consolidated table doesn't update because there are no formulas in the table.
However, it is possible to have the table update using VBA. There are two ways to accomplish this. 1) Place the macro in a regular code module and assign it to a "button" on the sheet which when clicked updates the table. [see attached file], 2) Place the code in the worksheet code window as a Worksheet_Change event. [sheet2 of attached file] The code was tweaked to include defining the target range. The following line was added as the first line:
If Intersect(Target, Range("B:B")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
This means that the code will only run (be triggered) if a cell in column "B" is changed.
Here is the macro:
Sub Consolidate_1()
' Consolidate_1 Macro
' macro written by WillieD24 for teachexcel.com
'Dim LRsrc As Long ' last row in source table
Dim LRcon As Long ' last row in consolidated table
'LRsrc = Range("A" & Rows.Count).End(xlUp).Row
LRcon = Range("D" & Rows.Count).End(xlUp).Row
' delete existing consolidated table data
Range("D2:E" & LRcon).ClearContents
Range("D2").Select
Application.CutCopyMode = False
' edit file path below to match your situation
Selection.Consolidate Sources:= _
"'C:\Users\USER\Documents\My Excel Stuff\TeachExcel Files\[ConsolTest1.xlsm]Sheet1'!R2C1:R11C2" _
, Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
Range("D1").Select
End Sub
If rows of data are added or removed from the source table then this line will need to have the R11 reference edited to match. (in both cases)
[ConsolTest1.xlsm]Sheet1'!R2C1:R11C2
If this solves your situation please mark the answer as selected.
Cheers :-)