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]

i just messaged you.

Please keep the conversation around this issue in the thread.
Thank you.

 

[Kaps_mr2]

hello Joe,

Here is the link:-

Joe - Google Drive

drive.google.com

there are two files. The automater v 1.xlsm is the one you need to run. A dialogue box then opens -asking for a file. You point it to the second file. The macro runs and creates a third workbook / pdf. thanks

 

[Joe4]

OK, I will take a look later.

One other thing, whay verion of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Joe4]

On your output file, which sheet was giving you the error when you tried to view the Print Range?

 

[Kaps_mr2]

no error but if you look at the pdf file there are blank pages - which suggests the print range is not being set properly.

 

[Kaps_mr2]

i have added an example of the pdf file to the drive and marked where there is blank space (which i am trying to get rid off).

 

[Joe4]

no error but if you look at the pdf file there are blank pages - which suggests the print range is not being set properly.

No, that is not what it implies at all. It implies that the range you are trying to print from each page is too big to fit on a single page, unless you reduce the "Zoom" size down or set each print range to fix on exactly one page.

As far as I can see, it is doing exactly what you are telling it to.

 

[Kaps_mr2]

thanks. I already have set the print range to 1 page:-

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

For sh = 1 To output_workbook.Sheets.Count
 
    With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:n28").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    
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]

I think Excel is having problems with your images, in trying to figure out how much room it needs to shrink your image by in order to get it to all fit on one sheet.
If I choose to use 95% Zoom instead of "Fit to 1 Sheet Wide by 1 Sheet Tall", the first bunch of pages print perfectly to one page. However, you have later sheets that have different column widths, so that does not work right for those ones.

If you want to keep all your images, and have different column widths per sheet, you may have to use different print settings for different sheets.
If you can at least group & identify similarly structured sheets, you might be able to set the appropriate print settings based on that.

 

[Kaps_mr2]

thanks. The problem was as you said - different column widths. As suggested, I grouped together sheets:-

For sh = 1 To output_workbook.Sheets.Count
 
 sheet_name = output_workbook.Sheets(sh).Name
 
 Select Case sheet_name
 
 Case "Front Cover"
    With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:n23").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    
Case "First Page"
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:c23").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    
    
Case "Do more of"
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:c23").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    
End With
    MsgBox "Do more of"
Case "Do less of"
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:c23").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

Case "Anything Else"
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:c23").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
    
    
Case "Questions for reflection"
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:c9").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
    
        
Case "Last Page"
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:d15").Address
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

        
Case Else
     With output_workbook.Sheets(sh).PageSetup
    .Orientation = xlLandscape
    .PrintArea = ""
    .PrintArea = output_workbook.Sheets(sh).Range("a1:n28").Address
End With


    
End Select