Pivot Table added measures created using a macro won't disappear

C

Chiwidan

New Member
Joined
Apr 19, 2020
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
Hi everyone,

I'm new to vba Macros in excel. I am creating a macro that will take the data from Table1 to create a Pivot Table.

There are a few measures I need to add to add, for example, names, email address and other non numerical data. For example "agent_email"

The macro will create the measures and add them to the Pivot Table.

If I run the macro for the first time, everything runs smoothly without issues but when I run it for a second time shows me error. It shows me that the measure is already created, even if a delete the sheet where the Table1 originally was, copy a new set of data and rename it as Table1. It looks to me there is some cache stored somewhere.


This is part of the code:

Dim MFATable As String
Dim MFASheetName As String
Dim MFAPivotSheetName As String

MFATable = ActiveSheet.ListObjects("MFATable").Name
MFASheetName = ActiveSheet.Name
MFAPivotSheetName = MFASheetName & " Pivot"

Range("MFATable[[#Headers],[Clienty Name & ID]]").Select
Sheets.Add
ActiveSheet.Name = MFAPivotSheetName



ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_Template for MF.xlsm!MFATable") _
, Version:=6).CreatePivotTable TableDestination:="'" & MFAPivotSheetName & "'!R3C1", TableName _
:="PivotTable1", DefaultVersion:=6
Sheets(MFAPivotSheetName).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = False
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = True
.CompactRowIndent = 1
.VisualTotals = False
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = True
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.DisplayEmptyRow = False
.DisplayEmptyColumn = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.DisplayImmediateItems = True
.ViewCalculatedMembers = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = True
.RowAxisLayout xlCompactRow
End With
ActiveSheet.PivotTables("PivotTable1").PivotCache.RefreshOnFileOpen = True
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").CubeFields("[MFATable].[MFA]")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

ActiveWorkbook.Model.ModelMeasures.Add "Client_Funding_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"TRIM(CONCATENATEX(MFATable,MFATable[Client funding], "" & ""))", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Client_Funding_List]")

ActiveWorkbook.Model.ModelMeasures.Add "NHI_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"TRIM(CONCATENATEX(MFATable,MFATable[Client NHI], "" ""))", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[NHI_List]")


ActiveWorkbook.Model.ModelMeasures.Add "Coach_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[Coach Name])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Coach_List]")


ActiveWorkbook.Model.ModelMeasures.Add "Email_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable)", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Email_List]")


ActiveWorkbook.Model.ModelMeasures.Add "Agent_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[Agent first name])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Agent_List]")


ActiveWorkbook.Model.ModelMeasures.Add "Plan_Start_Date_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[Support Plan Start Date])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Plan_Start_Date_List]")


ActiveWorkbook.Model.ModelMeasures.Add "Plan_End_Date_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[Support Plan Start Date])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Plan_End_Date_List]")


ActiveWorkbook.Model.ModelMeasures.Add "NASC_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[NASC])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[NASC_List]")


ActiveWorkbook.Model.ModelMeasures.Add "NASC_Email_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[NASC_email])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[NASC_Email_List]")


ActiveWorkbook.Model.ModelMeasures.Add "NASC_Coordinator_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[NASC_coordinator])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[NASC_Coordinator_List]")



ActiveWorkbook.Model.ModelMeasures.Add "Adjusted_Fortnights_Left_List", _
ActiveWorkbook.Model.ModelTables("MFATable"), _
"CONCATENATEX(MFATable,MFATable[Adjusted Fortnights Left])", ActiveWorkbook.Model. _
ModelFormatGeneral
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Adjusted_Fortnights_Left_List]")

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Sum of $ Budget]", xlSum, "Sum of Sum of $ Budget"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Sum of $ Budget]"), _
"Sum of Sum of $ Budget"

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Sum of Total Funds Remaining to date]", xlSum, "Sum of Sum of Total Funds Remaining to date"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Sum of Total Funds Remaining to date]"), _
"Sum of Sum of Total Funds Remaining to date"

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Receive Alerts]", xlAverage, "Average of Receive Alerts"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Average of Receive Alerts]"), _
"Average of Receive Alerts"


ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Adjusted Average Fortnightly Allocation]", xlSum, "Sum of Adjusted Average Fortnightly Allocation"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Adjusted Average Fortnightly Allocation]"), _
"Sum of Adjusted Average Fortnightly Allocation"


ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Adjusted Averaged Spend per fortnight]", xlSum, "Sum of Adjusted Averaged Spend per fortnight"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Adjusted Averaged Spend per fortnight]"), _
"Sum of Adjusted Averaged Spend per fortnight"

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Adjusted fortnightly Overspend]", xlSum, "Sum of Adjusted fortnightly Overspend"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Adjusted fortnightly Overspend]"), _
"Sum of Adjusted fortnightly Overspend"

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Adjusted Avg Fortnightly Spending]", xlSum, "Sum of Adjusted Avg Fortnightly Spending"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Adjusted Avg Fortnightly Spending]"), _
"Sum of Adjusted Avg Fortnightly Spending"

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Adjusted Risk this report]", xlSum, "Sum of Adjusted Risk this report"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Adjusted Risk this report]"), _
"Sum of Adjusted Risk this report"

ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
"[MFATable].[Risk last report]", xlSum, "Sum of Risk last report"
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").CubeFields("[Measures].[Sum of Risk last report]"), _
"Sum of Risk last report"


So, in my way of thinking, once I delete the pivot table, delete the Table1 and replace the source date, those PivotTable I added should dissapear, but they are still there...

I have tried refreshing the table and reset the source of the data...

please help... I'm puzzled

dan
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You must log in or register to reply here.

Similar threads

S
  • Question Question
Why won't a macro for a pivot table run on an identical workbook?
Replies
0
Views
764
superbuttz
S
larinda4
  • Solved
VBA Pivot Table: Adjusting Row and Column Numbers
Replies
4
Views
851
larinda4
larinda4
E
  • Solved
Excel VBA macro that creates a pivot table works for one user but not another
Replies
5
Views
2K
Eagle72
E
B
  • Solved
VBScript to create pivot for entire data on a tab
Replies
9
Views
3K
Domenic
D
S
  • Question Question
Recorded macro changes Pivot table layout when replayed.
Replies
2
Views
606
S Oberlander
S

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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