Inserting column in source data creates error in pivot table

[GreetingsJoe]

Greetings All!

I've searched many forums and sites with no success in an answer or workaround. I'm hoping someone on this forum could assist.

I'm trying to insert a column in the 'Input' tab do add another field to my pivot table on the 'Pivot' tab. Whenever I do that, it ends up giving me a #name error on the benefits calculated field on my pivot table.

IF anyone has any ideas or workarounds, please let me know. This is a very important field that needs to be added.

Thanks much for considering to help.

Joe

 

[p45cal]

This seems to be a bug which has cropped up from time to time.

My workaround:
1. Select a cell in the pivot table, and in the ribbon, PivotTable Tools, Options tab, Calculations section, Fields Items and sets dropdown, choose List Formulas.
This will add a new sheet with a bunch of stuff.

2. Add your new column in the source data, give it a header in row 13.

3. Refresh your pivot (all the errors will show up).

4. Add this macro to standard code module (eg.Module1):

Sub blah()
With Sheets("Pivot").PivotTables("PivotTable1")
  For Each cll In Sheets("Sheet1").Range("B3:B146").Cells
    .CalculatedFields(cll.Value).StandardFormula = cll.Offset(, 1).Value
  Next cll
End With
End Sub

but don't run it until you've done some checks:

edit post posting: I can't highlight the code above so here it is in plain text:
Sub blah()
With Sheets("Pivot").PivotTables("PivotTable1")
For Each cll In Sheets("Sheet1").Range("B3:B146").Cells
.CalculatedFields(cll.Value).StandardFormula = cll.Offset(, 1).Value
Next cll
End With
End Sub

• that the sheet that the pivot table is on is indeed called Pivot
• that the sheet with all those formulae on is indeed called Sheet1
  
• that the pivot table is called PivotTable1
• that the first calculated field name is in B3
• that the last calculated field name is in cell B146

If any of the above is not the case, adjust the code to reflect the differences.

Run the code - it takes a few seconds, and if the pivot table is visible you should see the errors disappearing.

Now you should do some checks:
1. Do the same as in 1 above - it will generate a new sheet.
2. Add a new sheet, and in cell A1 enter a formula:
=Sheet1!A1=Sheet2!A1
Adjust to the two sheet names with the formula lists on if necessary…
which you'll copy across and down to about cell D150.

If every cell contains TRUE then you're good to go.

Delete all added sheets, refresh the pivot to check no errors occur.

Attached has had the above applied.

 

[GreetingsJoe]

Thanks p45cal!

This is one of the most amazing workarounds I have ever seen. Thank you so much for dedicating your time and energy to helping me out! Amazing!!!!

 

[Mike_500]

Hi,

When I used your vb code in the original user's workbook, it ran as intended. But when I copied it to my own workbook and amended the code to reflect my sheet names and ranges, I got a "Compile Error: variable not defined" error at cll. Could you tell me what I'm doing wrong?

 

[p45cal]

Either remove :
Option Explicit
From the top of the code module,
or add:
Dim cll as range
at the top of the macro blah.

 

[Mike_500]

Either remove :
Option Explicit
From the top of the code module,
or add:
Dim cll as range
at the top of the macro blah.

Thank you very much for the quick reply. However, when I've amended the code as you recommended, I get a Runtime Error '9': Subscript Out of Range. What am I doing wrong?

Option Explicit

Sub blah()
Dim cll As Range
With Sheets("Sheet3").PivotTables("PivotTable2")
  For Each cll In Sheets("Sheet6").Range("C3:C5").Cells
    .CalculatedFields(cll.Value).StandardFormula = cll.Offset(, 1).Value
  Next cll
End With
End Sub

 

[p45cal]

Why did you change the column from B to C in:
For Each cll In Sheets("Sheet6").Range("C3:C5").Cells
?

 

[Mike_500]

Why did you change the column from B to C in:
For Each cll In Sheets("Sheet6").Range("C3:C5").Cells
?

Oh thank you for pointing that out!! I misread the original instructions, I was thinking that the Range referred to the location of the formulae rather than the NAME of the calculated field.

Your help is greatly appreciated!