pivot table gridlines (Excel 2003 / SP2)

Topic

New Reply

I can’t make the gridlines that I setup in the pivot tables permanently stay in place. I’ve tried to follow instructions (pasted below from the help screens), but the gridlines still disappear with each refresh. I’ve double-checked and the preserve formatting is on. I’ve noticed that the outside borders remain in the format that i’ve established so that is working, but the inside borders always disappear. What am I doing wrong?

Also note, I’ve tried changing the cell shading to a color and that will stay in place, but the borders won’t. This just doesn’t make sense.

Change other character and cell formatting

Make sure formatting will be preserved if you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report or change its layout: click the report, click PivotTable on the PivotTable toolbar, click Table Options, and then select the Preserve formatting check box.
Select the part of the PivotTable report you want to format.
If you want formatting applied to all such parts, so that when layout changes display parts not currently onscreen, those parts also have the formatting, click the (All) item in the dropdown lists for any page fields (page field: A field that’s assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.), and then make sure the mouse pointer changes to or before you click the part you’re selecting.

To apply the formatting changes you want, use the buttons on the Formatting toolbar and the commands on the Format menu.

Reply | Quote

Replies

You can create code that will format the pivot table automatically each time it is updated:
– Right-click the sheet tab of the worksheet that contains the pivot table.
– Copy/paste the following code into the worksheet module:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With Target.TableRange1.Borders
.LineStyle = xlContinuous
.ColorIndex = xlColorIndexAutomatic
.Weight = xlThin
End With
End Sub

– Modify as needed; you can change the style, color and width of the borders, for example.
– The above code will apply borders to the entire pivot table except the page field (if present).
– If you want to include the page field, use TableRange2 instead of TableRange1.

Reply | Quote

This is EXACTLY what I needed! It’s nice to know that I’m not crazy and doing something wrong like I usually do. Thanks for the code. Do you where I can get the full list to further customize? For example, if I wanted to change the borders to be blue instead of the automatic default, where would i get the code value for that? Thanks…as always, my hero Hans!

Reply | Quote

The best way to find out what code you need is to record a macro:
– Select some cells.
– Select Tools | Macro | Record New Macro…
– Click OK.
– Select Format | Cells…
– Specify the formatting that you want.
– Click OK.
– Click the Stop Recording button on the Record Macro toolbar, or select Tools | Macro | Stop Recording.
– Switch to the Visual Basic Editor to view the code that has been generated (there will be a new module under Modules).

Reply | Quote

Worked like a charm. You saved the day…again! Thanks!!!!!

Reply | Quote