Add print button to worksheet to print selected cells as a PDF document

[BlissC]

Hi all,

This is no doubt ridiculously easy if you know VBA, which I don't - I'm a complete beginner to VBA!

I've searched the web generally, and of course this forum first, and haven't been able to find anything that completely fits my situation. I've set up a spreadsheet that allows my team to record their time on a daily basis and allocate it to different projects, which then automatically completes a monthly sheet showing their worked time and how it's allocated. It's these monthly summaries of their time allocation that I want them to be able to print to send to the team manager.

What I'm trying to do is to add a print button to each worksheet to print the monthly time allocation sheet, a selected area of the sheet, as a PDF document. I've found the following code to print a selection, but can't find anything that saves to a PDF rather than to print.

[COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] PrintSomeCells()[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]Range([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"A1:I40"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]).PrintOut[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

Additionally, I'd like the selection to be fitted on one page (probably in landscape orientation rather than portrait). The users of the spreadsheet all have a PDF print driver installed on their PCs if that makes any difference.

I've figured out how to assign a macro to an object on the page to use as a print button, but it's the VBA code for the macro I'm struggling with. Any help would be appreciated.

Many thanks,

Bliss

 

[BlissC]

Just an update on this problem in case anyone else trying to do the same thing is looking for a solution. After scouting around some more online I found a site with a step-by-step guide on saving Excel as a PDF which includes a list of the PageSetup object properties and an explanation of what they do.

https://powerspreadsheets.com/save-excel-file-pdf-vba/

With a bit of cutting and pasting of bits of various code examples I came up with the code below. This simply creates a PDF of the selected cells in landscape format, fitting the cells onto 1 page and opens the resulting file to view. I simply assigned the macro to a rectangle shape on the page to create a button so the user can save their timesheet for each month as a PDF and view the page, and it's all working great.

Sub Save_Timesheet_As_PDF()

With ActiveSheet.PageSetup
    .CenterHeader = "Time Allocation Sheet"
    .Orientation = xlLandscape
    .PrintArea = "$A$1:$I$40"
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With


ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="Time Allocation Sheet", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


End Sub