LastRow = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row I am new to coding and need some help please I am not sure if it is because of something in my data range definition section or in the data field insertion section… I have copied and pasted the sections I feel might be where the issue is arising.
#How to create advanced pivot tables in excel code#
I used the generic code provided and did some edits to fit what I am attempting to do, the only issue is that my pivot table isn’t actually showing up in my new PivotTable worksheet. TableActiveSheet.PivotTables(“PivotTable1”).ShowTableStyleRowStripes = TrueActiveSheet.PivotTables(“PivotTable1”).TableStyle2 = “PivotStyleMedium9” With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Amount”) With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Frequency”) With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Applicable”) With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Threshold result”) (TableDestination:=PSheet.Cells(1, 1), TableName:=”PivotTable1″) Set PRange = DSheet.Cells(4, 2).Resize(LastRow, LastCol)ĬreatePivotTable(TableDestination:=PSheet.Cells(3, 1), _ I guess it is struggling to get the data from the source but honestly i’m not sure. , “Author”).Slicers.Add ActiveSheet,, “Author”, “Author”, 122.4, 496.2, 144, _įor some reason my code only seems to be creating the tab and no creating the pivot table. (TableDestination:=PSheet.Cells(1, 1), TableName:=”TeamQualityMetric1″)Ī2(ActiveSheet.PivotTables(“TeamQualityMetrics1”) _
(TableDestination:=PSheet.Cells(19, 1), TableName:=”TeamQualityMetrics”) Set PSheet = Worksheets(“Team Quality Metrics”)ĬreatePivotTable(TableDestination:=PSheet.Cells(20, 2), _ Worksheets(“Team Quality Metrics”).DeleteĪctiveSheet.Name = “Team Quality Metrics” Using this code I created 2 pivot tables in one sheet and trying to connect 1 slicer with both the tables but in Slicer Connection settings I don’t find option to select another table. With ActiveSheet.PivotTables(“SalesPivotTable”).PivotFields(“Local Legal Entity Code”) MissingItemsLimit = xlMissingItemsDefaultĪctiveSheet.PivotTables(“SalesPivotTable”).RepeatAllLabels xlRepeatLabels With ActiveSheet.PivotTables(“SalesPivotTable”).PivotCache With ActiveSheet.PivotTables(“SalesPivotTable”) “PivotTable!R1C1″, TableName:=”SalesPivotTable”, DefaultVersion:=6 PRange, Version:=6).CreatePivotTable TableDestination:= _ Set PRange = DSheet.Cells(1, 1).Resize(LastRow1, LastCol)Ī(SourceType:=xlDatabase, SourceData:= _ I have delete that part of the code and add it like this and it is working for me:
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount")ĪctiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = TrueĪctiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") _ĬreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ (SourceType:=xlDatabase, SourceData:=PRange). Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
0 kommentar(er)
