Recording macro pivot table error

B

biglb79

Active Member
Joined
Oct 17, 2007
Messages
303
Office Version
  1. 2019
Platform
  1. Windows
so I posted this on my other chain but I think it may go unnoticed since the original question was solved. I had a data tab that I wanted a macro to breakout into three different tabs. I then thought I could just record a macro while setting up the pivot tables, but there's an error when running the macro and I am not sure how to fix it. any help would be appreciated!

Basically I have a sequestered data tab that I want to make a sequestered pivot table tab, an ISNP data tab that I want to create an ISNP pivot table tab and a bad debt data tab that I want to create a bad debt pivot table tab. I placed the pivot table macro button on the sequestered key tab, so I don't know if that matters. below is the recorded macro and the error is in bold

Rich (BB code): 
Sub Pivot_tables()
'
' Pivot_tables Macro
'

'
    Range("A1").Select
    Sheets(Array("Sequestered Data", "ISNP Data", "Bad Debt Data")).Select
    Sheets("Sequestered Data").Activate
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1:AT1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("B1").Select
    Sheets("Sequestered Data").Select
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Sequestered category"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'sequestered key'!C[-10]:C[-9],2,0)"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K19969")
    Range("K2:K19969").Select
    Range("B1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sequestered Data!R1C1:R19969C47", Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="Sheet6!R3C1", TableName:="PivotTable18" _
        , DefaultVersion:=xlPivotTableVersion15
    Sheets("Sheet6").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable18").PivotFields("Facility Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable18").PivotFields("Sequestered category" _
        )
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable18").AddDataField ActiveSheet.PivotTables( _
        "PivotTable18").PivotFields("Amount"), "Sum of Amount", xlSum
    Columns("B:B").Select
    Selection.Style = "Comma"
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = "sequestered pivot table"
    Range("D31").Select
    Sheets("ISNP Data").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ISNP Data!R1C1:R33C46", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet7!R3C1", TableName:="PivotTable19", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet7").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable19").PivotFields("Facility Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable19").PivotFields("JE Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable19").AddDataField ActiveSheet.PivotTables( _
        "PivotTable19").PivotFields("Units"), "Sum of Units", xlSum
    ActiveSheet.PivotTables("PivotTable19").AddDataField ActiveSheet.PivotTables( _
        "PivotTable19").PivotFields("Amount"), "Sum of Amount", xlSum
    Columns("C:C").Select
    Selection.Style = "Comma"
    Range("C17").Select
    Sheets("Sheet7").Select
    Sheets("Sheet7").Name = "ISNP pivot table"
    Sheets("Bad Debt Data").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Bad Debt Data!R1C1:R232C46", Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="Sheet8!R3C1", TableName:="PivotTable20" _
        , DefaultVersion:=xlPivotTableVersion15
    Sheets("Sheet8").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable20").PivotFields("Facility Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable20").PivotFields("Payer Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable20").AddDataField ActiveSheet.PivotTables( _
        "PivotTable20").PivotFields("Amount"), "Sum of Amount", xlSum
    Columns("B:B").Select
    Selection.Style = "Comma"
    Sheets("Sheet8").Select
    Sheets("Sheet8").Name = "bad debt pivot table"
    Range("C4").Select
End Sub
 
Last edited by a moderator:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Hi @biglb79 , thanks for posting on the forum.

I'll explain what the problem is.
biglb79 said:
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sequestered Data!R1C1:R19969C47", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Sheet6!R3C1", TableName:="PivotTable18" _
, DefaultVersion:=xlPivotTableVersion15
Click to expand...
Before creating the pivot table, you are creating a sheet. At that time the sheet was named "Sheet6". So when you run the macro again, Sheet6 no longer exists and this is the error, surely when creating the sheet it is created with another name.

The solution is to always use the same name.
Here I present the solution. I attach the macro with the changes and with the deletion of several lines that the macro recorder puts, but they are not really necessary.

Replace your macro with the following macro:
VBA Code: 
Sub Pivot_tables()
  '
  ' Pivot_tables Macro
  '
  Dim lr As Long
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  '
  Sheets(Array("Sequestered Data", "ISNP Data", "Bad Debt Data")).Select
  Cells.EntireColumn.AutoFit
  Range("A1:AT1").Select
  With Selection
    .Font.Bold = True
    With .Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorDark1
      .TintAndShade = -0.249977111117893
      .PatternTintAndShade = 0
    End With
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
 
  On Error Resume Next
    Sheets("sequestered pivot table").Delete
    Sheets("ISNP pivot table").Delete
    Sheets("bad debt pivot table").Delete
  On Error GoTo 0

  '*******************************************************
  Sheets("Sequestered Data").Select
  Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("K1").FormulaR1C1 = "Sequestered category"
  Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'sequestered key'!C[-10]:C[-9],2,0)"
  lr = Range("L" & Rows.Count).End(3).Row
  Range("K2").AutoFill Destination:=Range("K2:K" & lr)
 
  Sheets.Add(, Sheets(Sheets.Count)).Name = "sequestered pivot table"
 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sequestered Data!R1C1:R" & lr & "C47", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="R3C1", TableName:="PivotTable18", _
    DefaultVersion:=xlPivotTableVersion15
 
  With ActiveSheet.PivotTables("PivotTable18")
    .PivotFields("Facility Name").Orientation = xlRowField
    .PivotFields("Facility Name").Position = 1
    .PivotFields("Sequestered category").Orientation = xlRowField
    .PivotFields("Sequestered category").Position = 2
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
  End With
  Columns("B:B").Style = "Comma"
 
  '*******************************************************
  Sheets("ISNP Data").Select
  lr = Range("L" & Rows.Count).End(3).Row
  Sheets.Add(, Sheets(Sheets.Count)).Name = "ISNP pivot table"
 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "ISNP Data!R1C1:R" & lr & "C46", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="R3C1", TableName:="PivotTable19", _
    DefaultVersion:=xlPivotTableVersion15
 
  With ActiveSheet.PivotTables("PivotTable19")
    .PivotFields("Facility Name").Orientation = xlRowField
    .PivotFields("Facility Name").Position = 1
    .PivotFields("JE Name").Orientation = xlRowField
    .PivotFields("JE Name").Position = 2
    .AddDataField .PivotFields("Units"), "Sum of Units", xlSum
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
  End With
  Columns("C:C").Style = "Comma"
 
  '*******************************************************
  Sheets("Bad Debt Data").Select
  lr = Range("L" & Rows.Count).End(3).Row
  Sheets.Add(, Sheets(Sheets.Count)).Name = "bad debt pivot table"
 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Bad Debt Data!R1C1:R" & lr & "C46", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="R3C1", TableName:="PivotTable20", _
    DefaultVersion:=xlPivotTableVersion15
   
  With ActiveSheet.PivotTables("PivotTable20")
    .PivotFields("Facility Name").Orientation = xlRowField
    .PivotFields("Facility Name").Position = 1
    .PivotFields("Payer Type").Orientation = xlRowField
    .PivotFields("Payer Type").Position = 2
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
  End With
  Columns("B:B").Style = "Comma"
 
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------


Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.
 
Upvote 0
Solution
DanteAmor said:
Hi @biglb79 , thanks for posting on the forum.

I'll explain what the problem is.

Before creating the pivot table, you are creating a sheet. At that time the sheet was named "Sheet6". So when you run the macro again, Sheet6 no longer exists and this is the error, surely when creating the sheet it is created with another name.

The solution is to always use the same name.
Here I present the solution. I attach the macro with the changes and with the deletion of several lines that the macro recorder puts, but they are not really necessary.

Replace your macro with the following macro:
VBA Code: 
Sub Pivot_tables()
  '
  ' Pivot_tables Macro
  '
  Dim lr As Long
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  '
  Sheets(Array("Sequestered Data", "ISNP Data", "Bad Debt Data")).Select
  Cells.EntireColumn.AutoFit
  Range("A1:AT1").Select
  With Selection
    .Font.Bold = True
    With .Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorDark1
      .TintAndShade = -0.249977111117893
      .PatternTintAndShade = 0
    End With
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
 
  On Error Resume Next
    Sheets("sequestered pivot table").Delete
    Sheets("ISNP pivot table").Delete
    Sheets("bad debt pivot table").Delete
  On Error GoTo 0

  '*******************************************************
  Sheets("Sequestered Data").Select
  Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("K1").FormulaR1C1 = "Sequestered category"
  Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'sequestered key'!C[-10]:C[-9],2,0)"
  lr = Range("L" & Rows.Count).End(3).Row
  Range("K2").AutoFill Destination:=Range("K2:K" & lr)
 
  Sheets.Add(, Sheets(Sheets.Count)).Name = "sequestered pivot table"
 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sequestered Data!R1C1:R" & lr & "C47", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="R3C1", TableName:="PivotTable18", _
    DefaultVersion:=xlPivotTableVersion15
 
  With ActiveSheet.PivotTables("PivotTable18")
    .PivotFields("Facility Name").Orientation = xlRowField
    .PivotFields("Facility Name").Position = 1
    .PivotFields("Sequestered category").Orientation = xlRowField
    .PivotFields("Sequestered category").Position = 2
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
  End With
  Columns("B:B").Style = "Comma"
 
  '*******************************************************
  Sheets("ISNP Data").Select
  lr = Range("L" & Rows.Count).End(3).Row
  Sheets.Add(, Sheets(Sheets.Count)).Name = "ISNP pivot table"
 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "ISNP Data!R1C1:R" & lr & "C46", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="R3C1", TableName:="PivotTable19", _
    DefaultVersion:=xlPivotTableVersion15
 
  With ActiveSheet.PivotTables("PivotTable19")
    .PivotFields("Facility Name").Orientation = xlRowField
    .PivotFields("Facility Name").Position = 1
    .PivotFields("JE Name").Orientation = xlRowField
    .PivotFields("JE Name").Position = 2
    .AddDataField .PivotFields("Units"), "Sum of Units", xlSum
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
  End With
  Columns("C:C").Style = "Comma"
 
  '*******************************************************
  Sheets("Bad Debt Data").Select
  lr = Range("L" & Rows.Count).End(3).Row
  Sheets.Add(, Sheets(Sheets.Count)).Name = "bad debt pivot table"
 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Bad Debt Data!R1C1:R" & lr & "C46", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="R3C1", TableName:="PivotTable20", _
    DefaultVersion:=xlPivotTableVersion15
  
  With ActiveSheet.PivotTables("PivotTable20")
    .PivotFields("Facility Name").Orientation = xlRowField
    .PivotFields("Facility Name").Position = 1
    .PivotFields("Payer Type").Orientation = xlRowField
    .PivotFields("Payer Type").Position = 2
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
  End With
  Columns("B:B").Style = "Comma"
 
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------


Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.
Click to expand...
Awesome! that does make sense and I did not think of that at all. This new code worked perfectly! thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
379
RoryA
RoryA
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
267
darmst24
D
S
  • Question Question
Help with Pivot Table Macro
Replies
0
Views
64
SCPbrito
S
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
273
Jette3173
J
S
  • Question Question
Macro for
Replies
2
Views
120
Sanderb
S

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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