Solved: Pivot Table Formatting

[Slicemahn]

Hi Everyone,

Does anyone have an example of some code that could speed up formatting on a pivot table. I am trying to be rid of that dreadful looking default view for pivot tables. For table options, the AutoFormat has been remove and Preserve cell formatting has been selected. I have written some code that would execute on a change in a worksheet and it takes forever to update. There are 5000 rows of data. Here's my code:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
' This macro written by
' September 7, 2007
'
'
' This macro will preserve cell formatting on the Pivot table
' each and every time it is refreshed
' This procedure will be placed in the Sheet 1 event as event when
' the Worksheet_Change event.
'
Dim ws As Worksheet
Dim RptArea As Range
Dim EndRow As Long
Dim EndCol As Integer
EndRow = Cells(65536, 1).End(xlUp).Row
EndCol = Cells(13, 256).End(xlToLeft).Column
Set RptArea = Range(Cells(14, 1), Cells(EndRow, EndCol))
With RptArea
.Cells.Borders.LineStyle = xlLineStyleNone
End With
End Sub[/VBA]

[rory]

Have you tried something like:

[VBA] With ActiveSheet.PivotTables(1).TableRange1
.Cells.Borders.LineStyle = xlLineStyleNone
End With
[/VBA]

[Slicemahn]

Rory,

Many thanks, this code executes a lot faster. Why would my code take so long?


Slice

[rory]

To be honest, I could not see anything there that I would expect to take a particularly long time, but using the pivot table's Tablerange properties (you could use tablerange2 if you wanted the headers) seemed easier.