What is the print area called and how to call it in VBA?

[DarkJester89]

I found this on search engine images but the print preview is what I have currently and it's not preferred for the user base because we can't adjust the printers or areas.

The code would for Sheet3, not active sheet but any advice is appreciated.

Sub PrintPreview()
Sheet3.PrintOut preview:=True

End Sub

Thanks in advance

 

[JoeMo]

Excel names the print area "Print_Area", a named range scoped to the worksheet level. In VBA you can treat it as a range like this:
Dim Rng as Range
Set Rng = Sheets("Sheet3").Range("Print_Area")

Note that you must include the worksheet since the named range is scoped to it.

 

[DarkJester89]

Sub PrintArea()
    Dim Rng As Range
    Set Rng = Sheets("Sheet3").Range("Print_Area")
End Sub

Run Time Error 1004 Applicated Defined or Object defined area with actual sheet name

Run time error 9, subscript out of range with Sheet3 name

 

[MARK858]

The 1st error normally means you haven't defined a print area so it is going by it's default setup.
The 2nd error normally means you haven't got a sheet named Sheet3.

 

[DarkJester89]

Think I would just to need to find a vba to set the print area, Column B to H (that's the usual area intended).

Worksheets("Sheet3").PageSetup.PrintArea = "B:H"

 

[MARK858]

And the row number or just the last row?
If just the columns try (untested)

Worksheets("Sheet3").PageSetup.PrintArea = Range("B:H").Address

 

[DarkJester89]

So that part works but now it won't call the Sheet3 to print, i have it coded, the command button clicks but doesn't do anything, doesn't debug or anything

 

[DarkJester89]

Private Sub CommandButton1_Click()
    Call SetArea
    Call PrintArea
End Sub

Sub SetArea()
    Worksheets("Sheet3").PageSetup.PrintArea = Range("D:L").Address
End Sub

Sub PrintArea()
    Dim Rng As Range
    Set Rng = Sheets("Sheet3").Range("Print_Area")
End Sub

 

[MARK858]

You're not telling it to PrintOut in either of those codes.
You need the line

WorkSheets("Sheet3").PrintOut

or

WorkSheets("Sheet3").PrintPreview

You also don't need the 2nd code if you are using the first, just

Sub SetArea()
     Worksheets("Sheet3").PageSetup.PrintArea = Range("D:L").Address
     WorkSheets("Sheet3").PrintOut
End Sub

 

[DarkJester89]

@MARK858 This works but is there a way I can get to the Print Prep screen for printer/setting designation. Activating this sends its straight to printer with no option selection.

Print Preview doesn't give the option to pick the printer/save to PDF. so the feature like this. Thank you in advance!