Auto Update Pivot Table and format wrap text

[Kevin]

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. 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 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 Autpen()
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
Columns("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!

 

[Dave Peterson]

What version of excel are you using?

If you're using xl97 or higher, you may want to dump the .onsheetactivate and
start using the worksheet_activate (for that particular sheet).

Right click on the worksheet tab that needs this done
select view code
use the dropdowns on the right hand side to choose Worksheet and Activate.

Excel will help you out with the shell of the procedure:

Private Sub Worksheet_Activate()

End Sub

Move your code from UpdateIt into there. (and kill the auto_open).

When you're done tweaking, your code could look something like:

Option Explicit
Private Sub Worksheet_Activate()

Dim iP As Integer

Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

'''Added Code to format D
With Range("D6" & Cells(Rows.Count, "D").End(xlUp).Row)
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

'You really want this?
With Range("D2")
.Select 'really change the location of the activecell???
.Locked = False
.FormulaHidden = False
End With

End Sub

Since this is code behind the one specific worksheet, it won't run unless the
worksheet is activated (from somewhere else).


But if you're running xl95, then you could modify your code like:

Option Explicit
Sub UpdateIt()

Dim iP As Integer

If LCase(ActiveSheet.Name) <> "sheet1" Then Exit Sub

Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True

'''Added Code to format D
With Range("D6" & Cells(Rows.Count, "D").End(xlUp).Row)
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

'You really want this?
With Range("D2")
.Select 'really change the location of the activecell???
.Locked = False
.FormulaHidden = False
End With

End Sub

It just checks the name of the activesheet--if it's not the right one, get the
heck out.

 

[Kevin]

Sorry for getting back so late, but haven't had a chance to read the post.
I have Version 2002 but I'll be running the spread sheet on Excel 2000.
I'll tried the macro and it works nicely, I did not know if there was a
command
to make the auto_open from the module only target a specific worksheet, but
instead I had to
add the code to the worksheet itself. I did try this an idea but never occur
to me
to take out the auto_open.
The part With Range("D2")
.Select 'really change the location of the activecell???
.Locked = False
.FormulaHidden = False
Was a mistake.

Thank you
Kevin