ISSUE: Refreshed Consolidated Tables causes (VBA) "SourceData:=" PivotTable Name to Change

[CJBills]

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> GOAL:
Linking SharePoint to Excel, so that Excel will contain pivots. Multiple SharePoint tables have been linked to individual sheets in Excel. A macro was written to consolidate the tables into another “MASTER” sheet, from which the data will be used for the pivot. A macro was written to create the pivots. All macros have been linked to a button on a blank sheet so that multiple users will be able to refresh the tables then create the pivots (following the creation of the MASTER sheet).

WHEN IT WORKS:
When the file is opened the first time, the macro runs correctly (because the name of the MASTER table hasn't changed, yet).

PROBLEM:
Each time the tables are consolidated into the MASTER sheet, the MASTER sheet TABLE name changes (from say "Table_1" to "Table_2"), preventing the pivots from being created. In VBA, the MASTER TABLE name is located following "SourceData:=" (see code below).

QUESTION:
How might I reference the table without referencing the MASTER TABLE name, which constantly changes?

'Create Pivot
    ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, _
        SourceData:="Table_1", _
        Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=Worksheets("MASTER").Range("A1"), _
        TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion12

What I’d like “SourceData:=” to reflect is (and I’m making this up, of course)

        SourceData:=(INPUT HERE CODING SO THAT NO MATTER WHAT THE TABLE NAME IS THIS CODE WILL WORK)

 

[mole999]

if you are build a nrew table every time, why not ensure the tables are deleted then build and name the table in code

 

[CJBills]

Presently, the sheet containing the table is deleted via macro. Then, the table is recreated. Problem is the table is renamed each time the table is recreated. Anyway around specifying the name of the table in the macro, following "SourceData:="?

Thanks

 

[mole999]

you can rename a table after it s inserted, and also try The VBA Guide To Excel Pivot Tables

 

[CJBills]

Update:

TABLE NAMING SOLUTION:

[COLOR=#008000]'Identify table [/COLOR]
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
[COLOR=#008000]'Rename table[/COLOR]
    ActiveSheet.ListObjects(1).Name = "Tbl_NAME"

"ActiveSheet.ListObjects(1)" was the table reference method I needed.

Thanks mole999 for the link. It is very helpful.

NEW PROBLEM:
Although I am able to change the name of the table, the pivot will work only the first time it is run via the button I've created. For some reason unknown (perhaps someone could enlighten), the pivot is not created as the script tells it to. The MASTER table is created via the button, but it seems the script gets to a point and then refuses to include the pivot.

SCRIPT
(NOTE: the button to run the entire script is on the PIVOT sheet, located somewhere on Rows("1:8"), which is the sheet where the script begins and thus no need to reference the PIVOT sheet initially):

[COLOR=#008000]'FIRST: Delete pivot in PIVOT sheet
'(NOTE: Is there another way to delete a single pivot, or multiple pivots if it's located in the same sheet?  Perhaps this is the problem.)
'[/COLOR]
    Rows("10:3000").Select
    Selection.Delete Shift:=xlUp
    Range("A13").Select
[COLOR=#008000]'
'SECOND: Delete "MASTER" sheet, if it exists
 '[/COLOR]
    Dim DelWs As Worksheet
    Application.DisplayAlerts = False
    Err.Clear
    On Error Resume Next
    Set DelWs = Sheets("MASTER")
    DelWs.Delete
[COLOR=#008000]'
'THIRD: Refresh data in TABLE1 and TABLE2 from their linked source
'
'(NOTE: I've omitted the THIRD step from the macro, run the REFRESH ALL button in the ribbon, then run the macro via the button to see if the issue arose from latency or something to do with the time it took to refresh the tables [I]AND [/I]run the macro, as if the processor forgot to run the last part of the macro because it was dumb; but knowing programs run poorly with poor written code, I imagine instead the issue must be with my lack of programming skills.  Perhaps I’m being too hard on myself.)
 '[/COLOR]
    ActiveWorkbook.RefreshAll
[COLOR=#008000]'
'FOURTH: Create "MASTER" sheet
'(NOTE: "Worksheets(4)" is because Worksheets(1) is "PIVOT", Worksheets(2) is "TABLE1", Worksheets(3) is "TABLE", and Worksheets(4) is "MASTER".)
'[/COLOR]
    Sheets.Add After:=Sheets(Sheets.Count)
    Worksheets(4).Name = "MASTER"
[COLOR=#008000]'
'FIFTH: Consolidate TABLE1 and TABLE2 into the newly created "MASTER" sheet
 'Add TABLE1 in "TABLE1" sheet to "MASTER" sheet
 ' 
[/COLOR]    Sheets("TABLE1").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("MASTER").Select
    Range("A1").Select
    ActiveSheet.Paste
[COLOR=#008000]'
 'Add TABLE2 in "TABLE2" sheet to "MASTER" sheet
' [/COLOR]
    Sheets("TABLE2").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("MASTER").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Activate
    ActiveSheet.Paste
[COLOR=#008000]'
'(NOTE: Actually, I am refreshing data for 10 tables on 10 sheets, then consolidating the data from all 10 tables, copying them into the "MASTER" sheet - perhaps this is an issue)
'
'SIXTH: Create pivot in "PIVOT" sheet from new table in "MASTER" sheet
 '
'Highlight table [/COLOR]
    Sheets("MASTER").Select
[COLOR=#008000]'Identify table[/COLOR]
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select  
[COLOR=#008000]'Rename table[/COLOR]
       ActiveSheet.ListObjects(1).Name = "Tbl_NAME"
[COLOR=#008000]'Create Pivot[/COLOR]
    ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, _
        SourceData:="Tbl_NAME", _
        Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=Worksheets("PIVOT").Range("A13"), _
        TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion12
    Sheets("PIVOT").Select
    Cells(13, 1).Select
[COLOR=#008000]'
'SEVENTH: Filter pivot
'  
'(NOTE: The pivot table won't even create, so I omitted this code)
'
[/COLOR]End Sub