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

[Chiwidan]

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