Facing problem while creating two pivot table in VBA.

[063azi]

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.

'
' 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

 

[GWteB]

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]

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]

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]

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

Unable to run macros after recording macros

Sub Macro7() ' ' Macro7 Macro ' ActiveCell.Offset(-2, -6).Range("A1").Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "PAF Folder!R1C1:R1048576C10", Version:=6).CreatePivotTable TableDestination _ :="City-Wise Payout!R3C1"...

www.mrexcel.com

 

[Fluff]

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).