Recording macro pivot table error

B

biglb79

Active Member
Joined
Oct 17, 2007
Messages
299
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
505
th259
T
A
  • Locked
  • Question
Macro to toggle data fields on pivot table
Replies
2
Views
487
Fluff
Fluff
C
  • Solved
Pivot Table Macro
Replies
2
Views
616
chaddres
C
Cubist
  • Solved
VBA - Need help optimize code that's updating pivot tables.
Replies
1
Views
88
Cubist
Cubist
U
  • Solved
Re-organizing pivot table fields in vba
Replies
2
Views
591
ummjay
U

Forum statistics

Threads
1,215,200
Messages
6,123,612
Members
449,109
Latest member
Sebas8956

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