Getting excel to default to "print entire workbook"

frewert

Board Regular
Joined
Apr 4, 2014
Messages
154
From what I have seen
Code:
sub pew()
ActiveWorkbook.PrintOut
end sub
Is the solution.

However, it has no effect on my workbook.

Code:
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

Code:
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe like this

Rich (BB code):
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

Board Regular
Joined
Apr 4, 2014
Messages
154

ADVERTISEMENT

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.
Code:
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

Code:
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

Code:
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

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Then try

Code:
ActiveWorkbook.Worksheets.Select

OR inside your loop that sents the print properties

Code:
ws.PrintOut
 

frewert

Board Regular
Joined
Apr 4, 2014
Messages
154
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,790
Messages
5,574,308
Members
412,586
Latest member
Medhum
Top