Getting excel to default to "print entire workbook"

[frewert]

From what I have seen

sub pew()
ActiveWorkbook.PrintOut
end sub

Is the solution.

However, it has no effect on my workbook.

Private Sub Workbook_Open()
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Filename = "W:\EPC Stationary and Logos\LOGOS\EPC Logos\Epc_logo_nobg.png"
        .Height = 100
        .Width = 150
        .Brightness = 0.36
        .ColorType = msoPictureGrayscale
        .Contrast = 0.39
        .CropBottom = 0
        .CropLeft = 0
        .CropRight = 0
        .CropTop = 0
    End With
    ' Enable the image to show up in the left header.
    ActiveSheet.PageSetup.RightHeader = "&G"
    ActiveSheet.PageSetup.RightFooter = "&P of &N"
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

Sub Printer()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        
        With ActiveSheet.PageSetup
'           .RightHeader = ""
            .RightFooter = "&P of &N"
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .Zoom = False
            .Orientation = xlLandscape
            .PrintArea = "$A$1:$G$40"
        End With
        
    Next ws
    ActiveWorkbook.PrintOut
    
End Sub

 

[VoG]

Maybe like this

Sub Printer()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        
        With ActiveSheet.PageSetup
'           .RightHeader = ""
            .RightFooter = "&P of &N"
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .Zoom = False
            .Orientation = xlLandscape
            .PrintArea = "$A$1:$G$40"
        End With
        
    Next ws
    Worksheets.Select
    ActiveWindow.SelectedSheets.PrintOut
    Sheets(1).Select
End Sub

 

[frewert]

Error at worksheets.select

 

[VoG]

What is the error? It works for me.

 

[frewert]

What is the error? It works for me.

Method "select" of object "sheets" failed.

I will post all of my code for the entire workbook so you understand completely.

I have command buttons for all of the copying subs you see here.

Sub blank_copy()
    
    Dim range1 As Range
    Set range1 = ThisWorkbook.Sheets("Sheet1").Range("A87:K127")
    
    range1.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Rows("1:57").Select
    Selection.RowHeight = 13
    ActiveSheet.Buttons.Add 749.25, 102.75, 132.75, 25.5
    ActiveSheet.Paste
    ActiveWindow.Zoom = 103
    
    Call Printer
    Call InsertPicture
    
End Sub
Sub tworow_copy()
    
    Dim range1 As Range
    Set range1 = ThisWorkbook.Sheets("Sheet1").Range("A44:K84")
    
    range1.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Rows("1:57").Select
    Selection.RowHeight = 13
    ActiveSheet.Buttons.Add 749.25, 102.75, 132.75, 25.5
    ActiveSheet.Paste
    ActiveWindow.Zoom = 103
    
    Call Printer
    Call InsertPicture
    
End Sub
Sub last_copy()
    
    Dim range1 As Range
    Set range1 = ThisWorkbook.Sheets("Sheet1").Range("M44:W84")
    
    range1.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Rows("1:57").Select
    Selection.RowHeight = 13
    ActiveSheet.Buttons.Add 749.25, 102.75, 132.75, 25.5
    ActiveSheet.Paste
    ActiveWindow.Zoom = 103
    
    Call Printer
    Call InsertPicture
    
End Sub
Sub onerow_copy()
    
    Dim range1 As Range
    Set range1 = ThisWorkbook.Sheets("Sheet1").Range("M1:W41")
    
    range1.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Rows("1:57").Select
    Selection.RowHeight = 13
    ActiveSheet.Buttons.Add 749.25, 102.75, 132.75, 25.5
    ActiveSheet.Paste
    ActiveWindow.Zoom = 103
    
    Call Printer
    Call InsertPicture
    
End Sub

Sub Printer()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        
        With ActiveSheet.PageSetup
'           .RightHeader = ""
            .RightFooter = "&P of &N"
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .Zoom = False
            .Orientation = xlLandscape
            .PrintArea = "$A$1:$G$40"
        End With
        
    Next ws
    Worksheets.Select
    ActiveWindow.SelectedSheets.PrintOut
    Sheets(1).Select
    
End Sub

Sub InsertPicture()
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Filename = "W:\EPC Stationary and Logos\LOGOS\EPC Logos\Epc_logo_nobg.png"
        .Height = 100
        .Width = 150
        .Brightness = 0.36
        .ColorType = msoPictureGrayscale
        .Contrast = 0.39
        .CropBottom = 0
        .CropLeft = 0
        .CropRight = 0
        .CropTop = 0
    End With
    ' Enable the image to show up in the left header.
    ActiveSheet.PageSetup.RightHeader = "&G"
End Sub

Private Sub Workbook_Open()
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Filename = "W:\EPC Stationary and Logos\LOGOS\EPC Logos\Epc_logo_nobg.png"
        .Height = 100
        .Width = 150
        .Brightness = 0.36
        .ColorType = msoPictureGrayscale
        .Contrast = 0.39
        .CropBottom = 0
        .CropLeft = 0
        .CropRight = 0
        .CropTop = 0
    End With
    ' Enable the image to show up in the left header.
    ActiveSheet.PageSetup.RightHeader = "&G"
    ActiveSheet.PageSetup.RightFooter = "&P of &N"
End Sub

This workbook is a template for control plans at work. When you open it, you have the template space, and to the right is an area with my command buttons that,
1. create a new sheet.
2. format the row widths and column widths.
3. paste a template

Right now all I need to do is make it so that the user doesn't have to switch from printing active sheet to printing entire workbook.

 

[VoG]

Then try

ActiveWorkbook.Worksheets.Select

OR inside your loop that sents the print properties

ws.PrintOut

 

[frewert]

ws.PrintOut actually prints out the active worksheet.

Also, error on ActiveWorkbook.Worksheets.Select

What I want is this.
The user will print as usual, by going to the File > Print method. I just want the default setting to be Print Entire Workbook so they don't have to toggle it on manually.