How do I create a PageSetup object

[KentBurel]

I have a routine that builds a workbook based on the number of sheets specified in a table. For each sheet I call a macro to build the PageSetup object for that sheet. It all works. But the PageSetup for each sheet is identical. So I want to define a myPageSetup object, build it one time, and then just assign it to each sheet. Here's my code. I get a type mismatch error on the Dim statement for myPageSetup. How do I create my own PageSetup object and assign it to each worksheet?

Option Explicit
Option Base 1
Sub BuildAllPrecinctSheets(Optional scope As String = "All") ' The default is to build all precinct sheets
    Dim Precincts As Variant
    Dim Cell As Variant
    Dim PrecinctNumber As String
    Dim PrecinctLocation As String
    Dim Number_of_Poll_Cards As Integer
    Dim Number_of_Poll_Pads As Integer
    Dim Number_of_BMDs As Integer
    Dim Number_of_Scanners As Integer
    Dim i As Integer
    Dim j As Integer
    Dim myStart As Integer
    Dim myStop  As Integer
    Dim myIndex As Integer
    Dim mySheet As Worksheet
    Dim ColumnHeaders As Variant
    Dim newHour As Variant, newMinute As Variant, newSecond As Variant, waitTime As Variant
    Dim myPageSetup As New Worksheet.PageSetup
    
' build the Page Setup object to set all the print parameters
    modBuildPageSetup.BuildPageSetup myPageSetup
    
    Application.Calculation = xlManual 'Turn off calculations for a bit
    Precincts = Range("Precincts").value
    ColumnHeaders = Range("ColumnHeaders").value
    
' Turn on application status bar
    Application.DisplayStatusBar = True
    
' Turn off screen updating
    Application.ScreenUpdating = False
    
' Set start and stop based on scope
    If scope = "All" Then
        myStart = 1
        myStop = UBound(Precincts)
    Else
        myIndex = 1
        For myIndex = 1 To UBound(Precincts)
            If Precincts(myIndex, 1) = scope Then
                myStart = myIndex
                myStop = myIndex
            End If
        Next myIndex
    End If
    
' This is where the main loop begins
    
    For i = myStart To myStop

' Update status bar each time thru
        Application.StatusBar = "Now building sheet " & i & " of " & myStop
        
' Wait one second for statusbar to update
        
        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 1
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
        
        PrecinctNumber = Precincts(i, 1)
        PrecinctLocation = Precincts(i, 2)
        Number_of_Poll_Cards = Precincts(i, 3)
        Number_of_Poll_Pads = Precincts(i, 4)
        Number_of_BMDs = Precincts(i, 5)
        Number_of_Scanners = Precincts(i, 6)
        Set mySheet = Sheets.Add(After:=ActiveSheet)
        mySheet.Select
        mySheet.Name = PrecinctNumber
        
' set the PageSetup object for each sheet to the one built above
        Set mySheet.PageSetup = myPageSetup
        
' Next build and format the sheet header.  This is the top 4 rows

        Range("B1:V1").Select
        Selection.HorizontalAlignment = xlLeft
        Selection.Merge
        Range("A2:V2").Merge
        Range("B1").Select
        ActiveCell.value = PrecinctNumber & " - " & _
                           PrecinctLocation
'
' Now the headers are ready complete with their data
'
        mySheet.Columns("A:A").ColumnWidth = 3
        mySheet.Columns("B").ColumnWidth = 8.75
        mySheet.Columns("C:Q").ColumnWidth = 5
        mySheet.Columns("R:U").ColumnWidth = 8.75
        mySheet.Columns("V").ColumnWidth = 6
        mySheet.Range("A3:V3").value = Application.WorksheetFunction.Transpose(ColumnHeaders)
        mySheet.Rows("3:3").RowHeight = 90
        Rows("3:3").Select
        With Selection.Font
            .Name = "Calibri"
            .Size = 8
            .Strikethrough = False
        End With
        With Selection
            .VerticalAlignment = xlBottom
            .WrapText = True
            .ShrinkToFit = False
        End With
        With mySheet.Range("A3:V3")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .Orientation = 90
        End With
        With mySheet.Range("B4").Resize(Number_of_BMDs, 1)
            .HorizontalAlignment = xlRight
        End With
        For j = 1 To Number_of_BMDs
            With Range("A4:B24")
                .Cells(j, 1).value = j
                .Cells(j, 2).value = "=VLOOKUP(""" & PrecinctNumber & "_" & j & """,BMDDATA,2,FALSE)"
                .Cells(j, 18).value = "=VLOOKUP(""" & PrecinctNumber & "_" & j & """,BMDDATA,3,FALSE)"
                .Cells(j, 19).value = "=VLOOKUP(""" & PrecinctNumber & "_" & j & """,BMDDATA,4,FALSE)"
                With Range("C4").Resize(Number_of_BMDs, 13)
                    .Cells.value = ChrW(168)
                    .Cells.Font.Name = "Wingdings"
                    .Cells.HorizontalAlignment = xlCenter
                End With
                With Range("C4").Offset(, 13).Resize(Number_of_BMDs, 1)
                    .Cells.value = "?"
                    .Cells.HorizontalAlignment = xlCenter
                End With
                With Range("C4").Offset(, 14).Resize(Number_of_BMDs, 1)
                    .Cells.value = ChrW(168)
                    .Cells.Font.Name = "Wingdings"
                    .Cells.HorizontalAlignment = xlCenter
                End With
                With Range("C4").Offset(, 15).Resize(Number_of_BMDs, 2)
                    .Cells.HorizontalAlignment = xlRight
                End With
                With Range("C4").Offset(, 17).Resize(Number_of_BMDs, 3)
                    .Cells.NumberFormat = "@"
                    .Cells.HorizontalAlignment = xlRight
                End With
            End With
        Next j
' Now only show the rows and columns that are relevant.  Don't show or print others.
        Application.EnableEvents = False
        Columns("W:W").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.EntireColumn.Hidden = True
        Rows(4 + Number_of_BMDs).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.EntireRow.Hidden = True
        Application.EnableEvents = True
    Next i
    Application.Calculation = xlAutomatic 'Turn Calculations back on
    Application.StatusBar = False 'Turn status bar off
    Application.ScreenUpdating = True 'Turn screen updating back on
    Sheets(2).Select
End Sub

 

[jkpieterse]

I'm afraid VBA isn't that flexible. You cannot simply assign an instance of a pagesetup object taken from worksheet A to worksheet B. You'll have to modify the pagesetup object of each worksheet individually and set all of its properties. That being said, if you set the page setup for a template sheet just the way you need it, then copying that worksheet creates a copy with the same page setup.

 

[KentBurel]

When I run this code I get a compile error - type mismatch Pointing to the Dim statement for myPageSetup.

 

[KentBurel]

OK. I'm learning the pluses and minuses of VBA. Thanks for your help.