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
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