Using VBA to name the ranges within a PivotTable

[SamBord]

Hi,

My Excel sheets need to have a number of conditional drop down lists. To do this I currently use a fairly lengthy process of =INDIRECT formulas with Data Validation.

I want to start using PivotTables more but I wish that each filed within a PivotTable was a named range that would update when the table is refreshed. I already use GETPIVOTDATA but I'd love to be able to run some code to name each row/column/data range.

I found an article here that I think should do what I want, but I can't get it to run.
http://yoursumbuddy.com/create-pivot-table-named-ranges/

Specifically Regex_replace function doesn't seem to work.

If anyone has any ideas or has already written something similar I'd be very grateful!

Thanks
Sam