change the value of active field in pivot table

Hi there and thanks in advance for your help.

I have question regarding changing value of active field in pivot table.

I have a pivot table in sheet2. The pivot table was generated using data in sheet1.

The pivot table shows the sum of sales made by Alan, Richard and May in year 1998 and 1999. The values were calculated from data provided in sheet1 which contains the monthly sales for each person.

1998 1999
Alan 20 30
Richard 40 55
May 35 60


Here is my pain, I want to prefix the sum of sales for Richard to be 30 for both year 1998 and 1999 regardless of the result returned from data in sheet1. The sum of sales for Alan and May are the summation of data in sheet1.

I wrote a VB script, but it return error "cannot change this part of a pivottable report".
Does anyone know how to solve this?

thanks,
Phoeberunner

You need to set the EnableDataValueEditing property of the PivotTable to True. Note that your values will get overwritten again if you refresh the table though.

You have to set it in your code.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save