Pivot Tables (through Power Pivot) Not Updating

[BrittKnee]

I have two parts to this:

1st: I have multiple pivot tables that I have created using Power Pivot so that I can get counts for specific categories over time (each Excel table has the historical data). When I click refresh all in Power Pivot, the new table is added, but not refreshed in the pivot table. Is there any way to refresh the pivot tables, so that the user can add data from the new table to the pivots?

2nd: Is there VBA to do the above? I have been trying to use the below code, however the new tables added will have a different name, so the table name in the code needs to be dynamic.

Any and all help is appreciated.

[Code/]
'Add New Table to Data Model

Set ws = ActiveSheet

With ws

lRow = .Range("A" & .Rows.Count).End(xlUp).Row
lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rng = .Range(.Cells(lRow, 1), .Cells(lRow, lCol))

rng.Select
End With

wrk.Connections.Add2 _
"WorksheetConnection_Vacancies_Report_A.xlsm!Vacancies_1014", "", _
"WORKSHEET;S:\Vacancy Project\Vacancies_Report_A.xlsm" _
, "Vacancies_Report_A.xlsm!Vacancies_1014", 7, True, False

[/code]
Thanks.