Pivot Table formatting

[Forgone]

Hi,

I'm currently working on a pivot table to group data based on the data
in another report, thus I'm not using the DATA or COLUMN fields and
everything is in the ROW field.

I've created the pivot table and have everything unchecked in the
PivotTable Options dialog box except for "Preserve formatting",
"Repeat item labels on each printed page" and "Save data with table
layout".

I used a macro to do the borders.... (included below) and when I
refresh the data, the xlHairLine borders aren't preserved and they all
return to xlThin borders.

Any ideas on how to retain the xlThin borders?

NB: This macro I use all the time to quickly do borders and is not
relative to the Pivot Table.




--------- BORDER MACRO -----------------

Sub borders()

Dim x As Variant
Dim wks As Worksheet
Set wks = ActiveSheet

x = ""

If wks.ProtectContents = True _
Or wks.ProtectDrawingObjects = True _
Or wks.ProtectScenarios = True Then

x = True
ActiveWorkbook.Unprotect
ActiveSheet.Unprotect
Else
End If

With Selection
.borders(xlDiagonalDown).LineStyle = xlNone
.borders(xlDiagonalUp).LineStyle = xlNone
With .borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
If .Columns.Count > 1 Then
With .borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
End If
If .Rows.Count > 1 Then
With .borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 15
End With
End If
End With

If x = True Then
ActiveWorkbook.Protect
ActiveSheet.Protect
Else
End If
End Sub

--------- BORDER MACRO -----------------

 

[Forgone]

any ideas?

 

[Bob Ryan]

I think if anyone can help, it's Debra Dalgleish at www.contextures.com. She
has an extensive website, and has also written 3 books on pivottables.