VBA - formatting a Pivot Table

purplefox

New Member
Joined
Jul 27, 2010
Messages
18
Hi,

I have a VBA question,

I'm trying to ensure the formatting of a pivot table (Excel 2003) by using a refresh VBA code activated by the user.

I have a code which can do this but it needs to select the sheet where the pivot table is.

the code I currently have is:

Code:
Sheet2.Select
    Sheet2.PivotTables("PivotTable3").PivotSelect "'Sum of Variance'", _
        xlDataAndLabel, True
    Selection.Interior.ColorIndex = 36
    Sheet2.PivotTables("PivotTable3").PivotSelect "'Ops/SMC'[All;Total]", _
        xlDataAndLabel, True
    Selection.Interior.ColorIndex = 47
    Sheet2.PivotTables("PivotTable3").PivotSelect "'Column Grand Total'", _
        xlDataAndLabel, True
    Selection.Interior.ColorIndex = 16

as you can see it uses "Pivotselect" and then "Selection" which means as in my code above the sheet2 needs to be selected.

if I want to copy a cell I might write sheet2.cells(1,1).copy rather than select then selection.copy, is there a similar change I can do to my code above (pressumably subsituting PivotSelect for something else?) so that I can run is successfully from any sheet without it needing to select sheet 2?

THanks in advance for your help,

Andy :)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi Dont know if this will help....

Private Sub formatAllPivotTableDataArea()
Dim ws As Worksheet
Dim pvtTable As PivotTable
Dim pvtField As PivotField

'don't allow screen updating because it takes time
Application.ScreenUpdating = False
'loop through each worksheet
For Each ws In ThisWorkbook.Worksheets
MsgBox ws.Name
'loop through each pivot table in the current worksheet
For Each pvtTable In ws.PivotTables

'This command changes the formatting of the data values area from "Count of" to "Sum of"
For Each pvtField In pvtTable.DataFields

'MsgBox pf
pvtField.Function = xlSum
pvtField.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next pvtField
'refresh the pivot table
pvtTable.PivotCache.Refresh
pvtTable.RefreshTable

Next pvtTable
'tell the user you are done with the refresh for each pivot table in current worksheet
MsgBox "The worksheet " & ws.Name & " and the pivot table are refreshed."

Next ws
End Sub
 
Upvote 0
Hi, thanks SilentBuddha,

I have managed to get around my problem in a different way which will be sufficient.

I'm not sure how I would edit your code to be able to select a particular set of calculation columns, or data to format individually.

Thanks for your reply,

Andy
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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