Improving code 1

[Gavona]

Any suggestions please as to how this code can be sped up or otherwise improved? Showpages creates around 110 sheets that need tweaking (insert some extra rows at the top, pagesetup for print settings, adjust column widths, set freezepanes).
Blue Bits: I guess I could somehow group all the sheets that meet that case and then make the change once.
Red bits: Is there a way that avoids the need to activate and select when freezing panes?
Black bits: Really this is what takes the time and would best benefit from your improvement ideas.

Sub ShowPages()
Dim sh As Worksheet
Dim c As Range
Dim strLF As String, strRF As String 'for footers

strLF = "&""Arial,Italic""&F  &A  on &D at &T"     'left
strRF = "Page  &P of &N"                           'right

'Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False

ActiveSheet.PivotTables("PivotTableMaster").ShowPages PageField:= _
        "Budget holder"

For Each sh In Sheets
    Select Case sh.Name
    Case "Budget Holder Detail", "Portfolio Detail"
        'Next sh
    Case Else
        [blue]sh.Rows("1:3").Insert Shift:=xlDown
        Worksheets("Budget Holder Detail").Range("3:4").Copy Destination:=sh.Range("A1")[/blue]
        With sh.PageSetup
            .PrintTitleRows = "$7:$7"
            .LeftFooter = strLF
            .RightFooter = strRF
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.5)
            .TopMargin = Application.InchesToPoints(1)
            .BottomMargin = Application.InchesToPoints(0.6)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            For Each c In Range("MyColWidth")
                sh.Columns(c.Column).ColumnWidth = c.Value
            Next c
        End With
        [red]sh.Activate
        sh.Range("I8").Select
        ActiveWindow.FreezePanes = True[/red]
    End Select
Next sh
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
End Sub

Gavin

 

[Gruuuu]

Yeah, setting pagesetup settings are always slow.
Can you create a "Template" sheet that has all of the Page Setup options set, and copy the data into the template, and copy that template off to a new sheet?

 

[Gavona]

Great idea. No reason why I shouldn't copy the pivot tables into a new sheet. Gets me thinking:

What template does the ShowPages use? Doesn't seem to be sheet.xlt...

Rather than use ShowPages (which is impressively fast) would I be better copying my original pivot table to multiple sheets and re-setting the page field value for each sheet?

Also been reading about xl4 macros

and about adding the line

ActiveSheet.DisplayPageBreaks = False

Not sure when I will get to testing out those ideas but I will do some testing someday. Have a star for thinking outside the box.
Makes me wonder

Gavin

 

[Gruuuu]

I guess it's cause I wasn't thinking about pivot tables... I don't get the chance to play with pivot tables that often

 

[SkipVought]

Gavin,

Just some general ideas.

When I configure a workbook for a particular application, that uses periodic updates based on other published workbooks, I grab the new data with MS Query or ADODB query, using a dynamic connection string. So my DESTINATION is always the same, with all the formatting, bells and whistles. I may spawn a workbook that is a subset of my master for general distribution. I more often just send users a LINK to my master (with a saved backup, naturally)


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}