Facing problem while creating two pivot table in VBA.

Status
Not open for further replies.

063azi

New Member
Joined
Jun 17, 2020
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey, I'm unable to create two pivot tables while recording macros, the first pivot perfectly runs but second pivot table showing below yellow highlighted error.
source data both pivots are the same
destination of both pivots on the same sheet but om different rows.


VBA Code:
'
' Macro1 Macro
'

'
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Datasheet = ActiveSheet.Name
Sheets.Add
NewSheet = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Datasheet & "!R1C1:R" & Finalrow & "C10", Version:=6).CreatePivotTable TableDestination:= _
NewSheet & "!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets(NewSheet).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("City")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Paid via Careem account")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Payment Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Payment Status"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Payment Status"). _
CurrentPage = "Paid"
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Datasheet = ActiveSheet.Name
Sheets.Add
NewSheet = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Datasheet & "!R1C1:R" & Finalrow & "C10", Version:=6).CreatePivotTable TableDestination:= _
NewSheet & "!R3C8", TableName:="PivotTable1", DefaultVersion:=6
Sheets(NewSheet).Select
Cells(3, 8).Select
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Amount"), "Sum of Amount", xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("City")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Payment Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Captain / Limo ID"), "Sum of Captain / Limo ID", _
xlSum
ActiveSheet.PivotTables("PivotTable2").PivotFields("Amount").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Paid via Careem account")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Payment Status"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Payment Status"). _
CurrentPage = "Paid"
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Sum of Captain / Limo ID")
.Caption = "Count of Captain / Limo ID"
.Function = xlCount
End With
End Sub

1592669482958.png


1592669515612.png
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,105
Office Version
  1. 2013
Platform
  1. Windows
Your code is not consistent. Macro 1 creates a pivot table called PivotTable1. Macro 2 also creates a pivot table with exactly the same name, although on a newly added worksheet, but in the rest of the code you refer to a pivot table with the name PivotTable2, which doesn't exist on that new worksheet, causing you a run-time error.
 

063azi

New Member
Joined
Jun 17, 2020
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Your code is not consistent. Macro 1 creates a pivot table called PivotTable1. Macro 2 also creates a pivot table with exactly the same name, although on a newly added worksheet, but in the rest of the code you refer to a pivot table with the name PivotTable2, which doesn't exist on that new worksheet, causing you a run-time error.
I have revorded this on 1 macros. This pivottable 2 name created auto. Can you change my codes so i can run 2 pivot tables.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,423
Office Version
  1. 365
Platform
  1. Windows
Cross posted Facing problem in creating 2 pivot table in macros!

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,105
Office Version
  1. 2013
Platform
  1. Windows
I have revorded this on 1 macros. This pivottable 2 name created auto. Can you change my codes so i can run 2 pivot tables.
Looks like @DanteAmor is already working on your problem
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,423
Office Version
  1. 365
Platform
  1. Windows
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,490
Messages
5,548,356
Members
410,828
Latest member
A9Bosv3
Top