Macro for Page Setup

[gallen6945]

Hi,

Please can someone help me.

I have a spreadsheet used often which I want to be able to automate the Page / Print Setup as opposed to manually setting it up each time.

Print area needs to start from A1
Last column will always be H
The last row will always fluctuate tho owing to data being greater or less than on the example

The orientation will need to be Portrait

Need this to be directed as an Active Sheet

Help greatly appreciated.

Thank you.

[6StringJazzer]

I suggest you turn on the macro recorder, then apply these settings. Then provide the code and we can help to make the code apply to the ActiveSheet.

To start printing in A1 through column H, simply set the print area to the entire columns A:H. It will automatically print to whatever is the last used row.

[jolivanes]

Or something like this.

Sub Maybe_So()
Dim lr As Long
lr = ActiveSheet.Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With ActiveSheet.PageSetup
    .PrintArea = Range("A1:H" & lr).Address
    .Orientation = xlPortrait
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
    ActiveSheet.PrintOut
End Sub

[gallen6945]

This is perfect - thank you.

Is there a way to add code which will generate a PDF file with the same file name and which is saved in the same folder location as the Excel file.

I have looked at numerous code and cannot fathom it.

[jolivanes]

Try so.

Sub Maybe_So_2()
Dim lr As Long, PDF As String
lr = ActiveSheet.Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
PDF = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".", -1, vbTextCompare))) & "pdf"
With ActiveSheet
        With .PageSetup
            .PrintArea = Range("A1:H" & lr).Address
            .Orientation = xlPortrait
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
    .ExportAsFixedFormat xlTypePDF, PDF
    .PrintOut
End With
End Sub

[gallen6945]

Hi,

That just prints the document. The PDF version wasn't created or saved as PDF.

I tried the exportasfixedformat all day yesterday and nothing worked.

It is probably a really simple piece of code but every website all seems to include something different.

Any ideas?

Thank you for your help.

[jolivanes]

It works at this end here.
It saves the PDF file in the same folder where the .xlsm workbook has been saved.
If you can't find the problem, attach the file here. Change personal data like names, addresses email addresses etc if present before attaching.

[gallen6945]

It outputs as a PDF to where my Personal.xlsb is.

C:\Users\xxxxx\AppData\Roaming\Microsoft\Excel\XLSTART

I need the location of the newly generated PDF file to be exactly the same as the Excel file I have opened.

[jolivanes]

Change "ThisWorkbook.FullName" to "ActiveWorkbook.FullName"