Preserving formula reference to pivot table

  • I have a macro which deletes worksheets and then recreates based off the report filter in the pivot table. Anytime I do this all references to the formulas used to gather the data especially the GetPivotData is lost and i have to fix all the #REF! errors manually. Is there a way to prevent this from happening? I used this code from contextures.


    TIA


  • Re: Preserving formula reference to pivot table


    The problem certainly lies with the sheet deletion, that is causing the #Ref in the formulas.


    Can you attach your workbook, impossible to suggest a solution without seeing the sheet(s) that get deleted and how the pivot table references data on those sheets.


    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Preserving formula reference to pivot table


    Here is the workbook. The dashboard is tab is where the formulas break when the sheets are recreated. Thanks for the help



    Quote from KjBox;790619

    The problem certainly lies with the sheet deletion, that is causing the #Ref in the formulas.


    Can you attach your workbook, impossible to suggest a solution without seeing the sheet(s) that get deleted and how the pivot table references data on those sheets.


    [sw]*[/sw]



    Here

  • Re: Preserving formula reference to pivot table


    I assume that by "dashboard tab" you mean the "TeamC" sheet. What I really need to see is what those formulas were before you ran the code, or what you have to manually correct them to.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Preserving formula reference to pivot table


    Unless there is far more to your workbook than you have shown with the sample file then all you need is the Raw Data sheet and put the Sales Pivot on the Dashboard sheet with a slicer for Teams.


    Then this code in the Raw Data Worksheet Object Module will update the Sales Pivot and its slicer for all changes to, or additions to, the Raw Data.


    Note I have made the Raw Data an Excel Built-in Table.


    You might need to change the width and height of the slicer, depending on what your actual team names are. You could also add another slicer for Names to give even more dashboard options.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!