To Apply formula in Pivot column

  • Re: To Apply formula in Pivot column


    Just checked this sample file , it works perfectly fine.


    Let me see, why is it not happening in the original file.


    Will keep u posted tommorrow in this. Thanks a ton again.


    Cheers,
    Alka

  • Re: To Apply formula in Pivot column


    Hi Rory,


    Still gets the error. I have increased the pivot size in the sample workbook and run the macro. Get the below error.
    Attached the file for your reference.
    Run-time error- 1004
    Application defined or object defined error.


    Also, wrt # 2. I need to run the code again everytime? What is worksheet's pivottableupdate event ? Is it a normal pivot refresh?
    Regards,
    Alka

  • Re: To Apply formula in Pivot column


    Hi Rory,


    Also, one more thing..
    Everytime I refresh the pivots, the cell formating is gone. I need to maintain this cell formating as below.
    1. Cell color should be white
    2. Cell format should be " Wrap Text"


    How can I code that.


    Regards,
    Alka

  • Re: To Apply formula in Pivot column


    Which cell formatting?
    This seems to be one of those expanding questions... ;)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: To Apply formula in Pivot column


    It wasn't the large table that was the problem, it was the one with no data showing. I've corrected that and added the update code so that when you refresh or alter a pivot table the code will run automatically. See attached.

  • Re: To Apply formula in Pivot column


    yeah,... was trying to ask something out of the original post. I was asking how to preserve the below formating of the pivot cell, everytime I refresh it is gone.
    1. Cell color should be white
    2. Cell format should be " Wrap Text"


    But you can choose to ignore this in this particular post.



    Just help me in the macro error. Really really appreciate the help done so far.

  • Re: To Apply formula in Pivot column


    Thanks Rory so much, the last cloumn" Request No" adds to the list perfectly.


    Do you mean, the code will automatically refresh the above added column if I alter pivot table? Right now it is not.


    Regards,
    Alka

  • Re: To Apply formula in Pivot column


    Yes - it should do, and does for me.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: To Apply formula in Pivot column


    Change ProcessPivot to:

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: To Apply formula in Pivot column


    Hi Rory,


    Thanks again.


    But my file is still throwing some errors. Attached the file with the errors. Sample_05012010.zip


    Thanks for all the effort & support.


    On a separate note, great work to support the nobel cause. Have to sponsor that.


    Regards,

  • Re: To Apply formula in Pivot column


    No file...

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: To Apply formula in Pivot column


    Can you not reduce the amount of data in it? I prefer not to give out my email address.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: To Apply formula in Pivot column


    You said you were getting an error, but there is no mention in that file of any errors? The reason you are not getting the macro run automatically is that you have not implemented the Workbook_SheetPivotTableUpdate code that I provided. I do not see any colour changes when I refresh the table either.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: To Apply formula in Pivot column


    You have two ProcessPivot routines in the file, which is why you are getting the error about ambiguous names. You need to remove the older version.
    Current version then needs to be amended to:


    or just get rid of the merged cells, since they serve no purpose.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

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