How do I create a PageSetup object

KentBurel

Board Regular
Joined
Mar 27, 2020
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
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?
VBA Code:
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
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,142
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

Board Regular
Joined
Mar 27, 2020
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
When I run this code I get a compile error - type mismatch Pointing to the Dim statement for myPageSetup.
 

KentBurel

Board Regular
Joined
Mar 27, 2020
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
OK. I'm learning the pluses and minuses of VBA. Thanks for your help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,482
Messages
5,770,349
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top