Excel Pivot. need help on Pivot Measure and the table name

[bobbyexcel]

I need help in creating a pivot table with Measure which is a part of my complete code. The issue is, whenever I create measure, it creates a model table with a unique name everytime. so my code is unable to read the variable (table name) to fetch data from the columns. Is there a way that I can define the table name and pass it as an argument while creating Pivot ?

Here is my code..


Dim chk As String
Dim mdlt As ModelTable
Dim strName As String

Set mdlt = ActiveWorkbook.Model.ModelTables(Index:=1)
chk = ActiveWorkbook.Model.ModelTables(Index:=1)
strName = ActiveWorkbook.Model.ModelTables(1).Name



Set PSheet = Worksheets("PivotTable_temp")
Set DSheet = Worksheets("Final_bkp")

lastRowNew = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastColNew = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastRowNew, LastColNew)

'Define Pivot Cache

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections("WorksheetConnection_Final_bkp!$A:$H1"), Version:=7).CreatePivotTable(TableDestination:=PSheet.Cells(3, 1), tableName:="MySTable", DefaultVersion:=7)


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(3, 1), tableName:="MySTable")
ActiveSheet.PivotTables("MySTable").PivotCache.RefreshOnFileOpen = False
ActiveSheet.PivotTables("MySTable").RepeatAllLabels xlRepeatLabels

With ActiveSheet.PivotTables("MySTable").CubeFields("[Range 2].[Server Name]")
.Orientation = xlRowField
.Position = 1
End With


I tried to pass the above with statment with the "strName" name as the table name but didnt work since the table name which is active is different than the output of the this (strName = ActiveWorkbook.Model.ModelTables(1).Name).. here is the with statement I tried

With ActiveSheet.PivotTables("MySTable").CubeFields("["strName"].[Server Name]")
.Orientation = xlRowField
.Position = 1
End With


Appreciate if anyone helps me on this..

This entire code is to keep a percentage column in pivot table ...

 

[bobbyexcel]

I need help in creating a pivot table with Measure which is a part of my complete code. The issue is, whenever I create measure, it creates a model table with a unique name everytime. so my code is unable to read the variable (table name) to fetch data from the columns. Is there a way that I can define the table name and pass it as an argument while creating Pivot ?

Here is my code..


Dim chk As String
Dim mdlt As ModelTable
Dim strName As String

Set mdlt = ActiveWorkbook.Model.ModelTables(Index:=1)
chk = ActiveWorkbook.Model.ModelTables(Index:=1)
strName = ActiveWorkbook.Model.ModelTables(1).Name



Set PSheet = Worksheets("PivotTable_temp")
Set DSheet = Worksheets("Final_bkp")

lastRowNew = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastColNew = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastRowNew, LastColNew)

'Define Pivot Cache

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections("WorksheetConnection_Final_bkp!$A:$H1"), Version:=7).CreatePivotTable(TableDestination:=PSheet.Cells(3, 1), tableName:="MySTable", DefaultVersion:=7)


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(3, 1), tableName:="MySTable")
ActiveSheet.PivotTables("MySTable").PivotCache.RefreshOnFileOpen = False
ActiveSheet.PivotTables("MySTable").RepeatAllLabels xlRepeatLabels

With ActiveSheet.PivotTables("MySTable").CubeFields("[Range 2].[Server Name]")
.Orientation = xlRowField
.Position = 1
End With


I tried to pass the above with statment with the "strName" name as the table name but didnt work since the table name which is active is different than the output of the this (strName = ActiveWorkbook.Model.ModelTables(1).Name).. here is the with statement I tried

With ActiveSheet.PivotTables("MySTable").CubeFields("["strName"].[Server Name]")
.Orientation = xlRowField
.Position = 1
End With


Appreciate if anyone helps me on this..

This entire code is to keep a percentage column in pivot table ...

Or if there is no answer, then I would like to duplicate the existing column and do the calculation without using measure but using the below function.. but I need help in this function.

```````
With ActiveSheet.PivotTables("PivotTableName").PivotFields("ColumnName")
.Orientation = xlDataField
.Function = xlSum
.Calculation = ["Total Success"] / ["Total Jobs"]
.NumberFormat = "0.00%;-"
End With
```````

 

[bobbyexcel]

any help would be appreciated ..