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

CJBills

New Member
Joined
Apr 27, 2015
Messages
13
<!--[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?

Code:
'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)

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
if you are build a nrew table every time, why not ensure the tables are deleted then build and name the table in code
 
Upvote 0
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
 
Upvote 0
Update:

TABLE NAMING SOLUTION:

Code:
[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):

Code:
[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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top