PDA

View Full Version : vba to clear values in an excel pivot table



noetic76
05-11-2015, 10:23 PM
Heya!

I have a pivot table that is accessing a table with numerators and denominators for calculation of several measures. I want users to be able to click on a button to select the measure they wish to see, after they have selected some filters from a couple of slicers.

I've got the code that selects the measure for the pivot table values, but I need to clear the value that's in the table first. I could use the "Clear All" functionality in the PivotTable Tools, but that clears the filters too.

Here's the code for the measure selection, can you help me to add a bit at the beginning that clears the value currently in the pivot table please?


Sub measure()

ActiveSheet.PivotTables("es1").AddDataField ActiveSheet.PivotTables("es1"). _
PivotFields("total waiting"), "Sum of total waiting", xlSum
With ActiveSheet.PivotTables("es1").PivotFields("Sum of total waiting")
.NumberFormat = "#,##0"
End With

End Sub

Thanks all!
Ruth

Yongle
05-12-2015, 02:02 AM
Hi Ruth
Have you read the forum FAQ (extract below)

What is multiposting?

Multiposting is the act of posting a single question to different forums around the same time.

We discourage multiposting because it is like calling five cab companies and going with the one that comes first -- it shows disrespect for the volunteers that monitor this forum because they will unknowingly be helping someone who may have already received help elsewhere. Their time is no less important than that of the person asking the question.

Many of the volunteers here visit multiple forums and can easily spot duplicate posts. Some of them may even reply to your posts with a link to your post on another forum. Don't be the person that gets caught.

If you must post your question on a different forum, include a link to the question you have already posted on the previous forum(s). That way, those helping you can decide for themselves if you are already receiving the help you need somewhere else.



If you are still confused, read A message to forum cross posters (http://www.excelguru.ca/node/7).




Cross posted here (http://www.mrexcel.com/forum/excel-questions/854428-visual-basic-applications-clear-values-pivot-table.html)