Macro To Place User Defined Percent Into Pivot Calculation

ggelineau

Board Regular
Joined
May 24, 2005
Messages
69
I am working with a Pivot Table that contains a calculated field which is simply revenue multiplied by a percent. The formula is hard coded within the Pivot Table, but users would like to be able to manipulate the percent used within the formula without having to manually change the formula itself (i.e. they would like to be able to enter a percent into a cell that exists above the pivot table and the formula within the pivot table will then recalculate using the user defined percent).

I have been told that I need to use a macro to acheive this as a pivot table calculated field can not refer to a cell.

I have messed around and created the following VBA code thus far, but when I enter a number into my cell I get an error saying that "The Value You Entered Is Not Valid; A user has restricted the values that can be entered into this cell." The below code is supposed to change the formula "EOTC Credit" that is found in the Pivot Table within the worksheet "Active Pivot" so that the percent entered into cell F8 on the "Active Pivot" worksheet is entered into the "EOTC Credit" formula. Cell F8 is a defined range titled var_EOTCCredit. The original "EOCT Credit" formula is (='SumOfFinal Imputed List Revenue' *0.01), where "SumOfFinal Imputed List Revenue is a field within the data that the pivot is built off of and "0.01" is the percent that was manually placed into the formula to start with. It is this percent that I would like to replace based on what the user defines.

Not sure if the code below can be fixed or just scrapped.....please help!!!!
_________________________________________


Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Address = Target.Address Then
ActiveSheet.PivotTables(1).CalculatedFields("EOTC Credit").StandardFormula = "='SumOfFinal Imputed List Revenue' *" & ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Value & ")"
End If
End Sub
_________________________________________________________________________

Any help is VERY MUCH APPRECIATED!!!!

Thank you,
G
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
For those who are looking for the code to acheive this or something similar, here is the code I created in order to resolve the situation I noted in my original post:


In my Pivot Table, I had the following Calculated Field: Revenue [MULTIPLIED BY] Percent
Users of the Pivot Table wished to define the Percent Used in the Calculated Field, which is a difficult task as Excel Pivot Table Calculated Fields do not allow cell references.
The following Macro was written in order to achieve my needs:

CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
' Changes the Pivot table formulae based on the percents entered
'
' Note: Needs named range
' - 'var_EOTCCredit' helps indicate change of EOTC Credit percent
' & acts as variable to transfer % to updated calculated field (EOTC Credit)
' I.E. When User enters % into field (named range), macro runs and
' updates calculated field based on what was entered
'

If ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Address = Target.Address Then
ActiveSheet.PivotTables(1).CalculatedFields("EOTC Credit").StandardFormula = "='Revenue' *" & ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Value
End If
End Sub


Hopefully this can be of help...
G
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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