Auto Update Pivot Table and format wrap text

Giganews Newsgroups
Subject: Auto Update Pivot Table and format wrap text
Posted by:  Kevin (kevin.brenn…@worldnet.att.com.RemoveMe!)
Date: Thu, 30 Oct 2003

I have this code in a module, so as to refresh automatically a pivot table
in a sheet named "PrintSheet" it works fine in till I added some extra code
to format the cell D:D. What happens is now it tries to format every sheet I
open, I only what to format every time I click on the "PrintSheet" and
not in all the other sheets, and also need it to format from range D6:D not
the complete column.
I found out that the pivot table does not keep its  format properties, so
when new data is added it need to be formatted everytime, that is why I like
to do this auto refresh/format.

Sub Auto_Open()
Application.OnSheetActivate = "UpdateIt"
End Sub
Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
  ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

'''Added Code to format D:D
Columns("D:D").Select
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("D2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
End Sub

Thanks for your help!

Replies