Unable to run macros after recording macros

0

063azi

New Member
Joined
Jun 17, 2020
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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", TableName:="PivotTable11", DefaultVersion:=6
Sheets("City-Wise Payout").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable11")
.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("PivotTable11").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable11").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable11").PivotFields("City")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
"PivotTable11").PivotFields("Amount"), "Sum of Amount", xlSum
With ActiveSheet.PivotTables("PivotTable11").PivotFields("Payment Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable11").PivotFields("City")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable11").PivotFields("Payment Status"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable11").PivotFields("Payment Status")
.PivotItems("Failed").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable11").PivotFields("Payment Status"). _
EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable11").PivotFields( _
"Paid via Careem account")
.Orientation = xlColumnField
.Position = 1
End With
End Sub

can any body help me I'm unable to run macros after recording creating the Pivot table facing this yellow highlighted msg!
 

Attachments

  • Capture.JPG
    Capture.JPG
    81.5 KB · Views: 21

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
You should also mention the description of the error.

' Apostrophes are missing in the names of the sheets:

Rich (BB code): 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'PAF Folder'!R1C1:R1048576C10", Version:=6).CreatePivotTable TableDestination _
:="'City-Wise Payout!R3C1'", TableName:="PivotTable11", DefaultVersion:=6
 
Upvote 0
DanteAmor said:
You should also mention the description of the error.

' Apostrophes are missing in the names of the sheets:

Rich (BB code): 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'PAF Folder'!R1C1:R1048576C10", Version:=6).CreatePivotTable TableDestination _
:="'City-Wise Payout!R3C1'", TableName:="PivotTable11", DefaultVersion:=6
Click to expand...

Let me add this and try to run it but i haven't delete the apostrophes, why are these missing or i have to add every time after recording pivot table steps in macros ?
 
Upvote 0
1592614416136.png



after adding apostrophes its still showing error!
 
Upvote 0
DanteAmor said:
You should also mention the description of the error.
Click to expand...

In addition to showing which line the macro stops on, you must write the error message.

In the destination sheet do you have pivot tables?
The sheet is protected?
It would be convenient if you provide more information. Help us to help you.

We do not know your sheet, or how your data is, if you already have tables or if one already exists with the same name, if you have merged cells, the name of the sheets, etc., etc.
 
Upvote 0
DanteAmor said:
In addition to showing which line the macro stops on, you must write the error message.

In the destination sheet do you have pivot tables?
The sheet is protected?
It would be convenient if you provide more information. Help us to help you.

We do not know your sheet, or how your data is, if you already have tables or if one already exists with the same name, if you have merged cells, the name of the sheets, etc., etc.
Click to expand...


I have data in sheet 1 and created the pivot table in sheet 2 while recording macros now want to run the same but it shows error of debugging

1592651848970.png
.

1592651880944.png
 
Upvote 0
DanteAmor said:
In the destination sheet do you have pivot tables?
The sheet is protected?
It would be convenient if you provide more information. Help us to help you.

We do not know your sheet, or how your data is, if you already have tables or if one already exists with the same name, if you have merged cells, the name of the sheets, etc., etc.
Click to expand...
 
Upvote 0
Hey im creating now pivot table but i have to create two pivot table while im recording macros first pivot perfectly run but second pivot table showing below yellow highlighted error.
source data both pivots are same
destination of both pivot are on same sheet but different row.


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

1592667592134.png
 

Attachments

  • 1592667534953.png
    1592667534953.png
    21.9 KB · Views: 4
Upvote 0
It is difficult to help you without not answering the questions raised.

In the destination sheet do you have pivot tables?
The sheet is protected?
It would be convenient if you provide more information. Help us to help you.

We do not know your sheet, or how your data is, if you already have tables or if one already exists with the same name, if you have merged cells, the name of the sheets, etc., etc.
 
Upvote 0
DanteAmor said:
It is difficult to help you without not answering the questions raised.

In the destination sheet do you have pivot tables?
The sheet is protected?
It would be convenient if you provide more information. Help us to help you.

We do not know your sheet, or how your data is, if you already have tables or if one already exists with the same name, if you have merged cells, the name of the sheets, etc., etc.
Click to expand...


No sheet is not protected and yes I'm creating 2 pivots on the same sheet 1 perfectly run but haveing problem in the second one.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
676
Steve48
S
C
  • Solved
VBA Pivot Table
Replies
2
Views
637
chaddres
C
S
  • Question
Not able to Create Pivot Table
Replies
1
Views
355
RoryA
RoryA
B
  • Solved
Recording macro pivot table error
Replies
2
Views
1K
biglb79
B
J
  • Question
VBA macro to create pivot table on sheet2 with data source on sheet1
Replies
9
Views
1K
SpillerBD
S

Forum statistics

Threads
1,216,119
Messages
6,128,946
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top