PDA

View Full Version : Solved: Pivot Table Formatting



Slicemahn
09-10-2007, 08:47 AM
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: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

rory
09-10-2007, 09:04 AM
Have you tried something like:

With ActiveSheet.PivotTables(1).TableRange1
.Cells.Borders.LineStyle = xlLineStyleNone
End With

Slicemahn
09-10-2007, 09:20 AM
Rory,

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


Slice

rory
09-10-2007, 03:30 PM
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.