set print area for all sheets using vba

[Kaps_mr2]

Hello,

I am trying to set the print range on each sheet on my workbook. However the following does not work:-

For sh = 1 To output_workbook.Sheets.Count

    output_workbook.Sheets(sh).PageSetup.PrintArea = Range("a1:n28").Address
    output_workbook.Sheets(sh).PageSetup.Orientation = xlLandscape
    
    
     
Next sh

thanks
Kapa

 

[Joe4]

What if you change this line:

    output_workbook.Sheets(sh).PageSetup.PrintArea = Range("a1:n28").Address

to this:

    output_workbook.Sheets(sh).PageSetup.PrintArea = output_workbook.Sheets(sh).Range("a1:n28")

 

[Kaps_mr2]

thanks - that prints the range("a1:n28") on one page and the rest of the sheet on another page. The final output will be viewed as a pdf file. I am doing the pdf via excel rather than vba.

 

[Joe4]

Sorry, I forgot the ".Address" at the end of my line, i.e.

    output_workbook.Sheets(sh).PageSetup.PrintArea = output_workbook.Sheets(sh).Range("a1:n28").Address

 

[Kaps_mr2]

hello - thanks.

So i now have as per below - but am still getting some sheets that ignore the defined print area.

Public Sub SaveActiveWorkbookAsPDF()
Dim output_path As String
Dim strpathfile As String
Dim sh As Integer

For sh = 1 To output_workbook.Sheets.Count
 
    output_workbook.Sheets(sh).PageSetup.Orientation = xlLandscape
    output_workbook.Sheets(sh).PageSetup.PrintArea = output_workbook.Sheets(sh).Range("a1:n28").Address
      
Next sh

output_path = ThisWorkbook.path
strpathfile = output_path & "\output.pdf"


Application.DisplayAlerts = False

output_workbook.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strpathfile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
  
  
End Sub

 

[Joe4]

Where exactly is "output_workbook" being defined/set?

Also, are you trying to print this to one PDF file, or multiple PDF files?

 

[Kaps_mr2]

hello,

Output_workbook is being defined at the top of the module.

I would like it as one file. thank you.

kind regards
Kaps

 

[Joe4]

It seems to work just fine for me.
Do you have other code overwriting your print ranges?
Are you sure that your data is not just spilling over to multiple pages?

 

[Kaps_mr2]

There is other data on the sheets but I don't want that printed. How can i check if there is other code overwriting my print ranges ?

 

[Kaps_mr2]

is an example of the output. I want range "a1:n28" from each sheet.