There are two straightforward methods to save lots of the lively Microsoft Excel sheet to a PDF file: manually and with a macro. Here is tips on how to do each.

Picture: iStock/jurgenfr

We used to print charts and different evaluation reviews to share with others. Right now, we are able to bypass the printing route by sharing reviews electronically. Nonetheless, sending the complete Microsoft Excel workbook file is normally inappropriate; you will seldom wish to share the supply information together with reviews. One method to share solely the report is to save lots of that sheet to a PDF file after which share it electronically—no fuss, no muss, no ready for a printout to route via an archaic in-house mail system. On this article, I will present you the way straightforward it’s to save lots of solely the lively sheet to a PDF file through the use of the interface and by operating a macro.

SEE: 69 Excel ideas each consumer ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system. You may work with your personal information or obtain the demonstration .xlsx file. This text is not acceptable for the browser version nor the sooner .xls format.

The report vs. the spreadsheet

If you happen to’re like many customers, you separate information from reviews. That method, you possibly can rapidly print a report with out additionally sharing the supply information. It is fast and clear. Determine A exhibits such a reporting sheet. If you happen to ship the complete workbook electronically, others have entry to the supply information, which may be confidential or proprietary. Moreover, together with the info in a report is distracting. 

Determine A


  Typically, we share reviews, however not the supply information.

As talked about, you possibly can print the report and route it appropriately, however as a substitute, let’s put it aside to a PDF file you can share by e-mail, on OneDrive, or attaching by way of a chat window or Crew assembly. We cannot spend time on creating the report sheet, so we are able to think about saving the report sheet. (If you happen to’re utilizing the demonstration file, you possibly can view the supply information within the Stratification tab.)

Easy methods to handbook save as a PDF in Excel

Saving the report sheet to a PDF file is the straightforward half. From the report sheet, click on the File tab. Within the left pane, select Save As or Save a Copy (OneDrive). Within the ensuing dialog, select PDF (*.pdf) from the Save dropdown (Determine B), after which click on Save. You can too change the folder or filename when crucial. You may see the ensuing PDF file in Determine C. If the workbook has extra sheets (and most will), this course of saves solely the lively sheet.

Determine B


  Save the sheet as a PDF file.

Determine C


  The PDF file incorporates information solely from the lively sheet.

Easy methods to use a macro to save lots of as a PDF in Excel

You in all probability did not want me to indicate you tips on how to save a sheet to a PDF file however realizing that the duty saves solely the present sheet may be new to you. If you happen to do that usually, you may profit from a macro that saves the present sheet to a PDF file—it solely takes one line of code. So as to add the code, do the next:

  1. Press Alt+F11 to open the Visible Fundamental Editor (VBE).
  2. Discover Sheet2 (Stratification) within the Venture Explorer to the left (replace the sheet title for those who’re working with your personal file). Double-click to open the sheet’s module.
  3. Enter the code in Itemizing A into the sheet module (Determine D). Do not copy from this net web page. You may import the demonstration .bas file or enter the code manually.

Itemizing A

Sub SaveReportPDF()

‘Save Report sheet (Sheet2) as pdf file.

  ExportAsFixedFormat xlTypePDF

Finish Sub

Determine D


  Enter the sub process in Itemizing A.

Subsequent, return to Excel and save the file as a macro-enabled file. To run the macro, click on the Developer tab, click on Macros within the Code group, after which select the SaveReportPDF sub process and click on Run.

If the Developer tab is not seen, you could find directions within the article Easy methods to add Workplace macros to the QAT toolbar for fast entry. (See the primary paragraph following Determine J.)

The brief process does the bear minimal and makes loads of choices for you. As is, this process saves the lively sheet (Stratification) to a PDF file utilizing the title of the Excel file to call the PDF file, and saves that file in the identical folder. It takes care of quite a bit for you, however you may want to regulate issues a bit. The syntax for the total command is

ws.ExportAsFixedFormat (KindFileNameHigh qualityIncludeDocPropertiesIgnorePrintAreasFromToOpenAfterPublishFixedFormatExtClassPtr)

the place solely Kind is required. In our case, we used the fixed xlTypePDF and left the OpenAfterPublish as its default, true. The title of the demonstration workbook is ExcelPDF_demo.xls, so the macro saves the Stratification sheet to a PDF file named ExcelPDF_demo.pdf in the identical folder because the workbook file, after which opens the newly saved PDF file. You may discover the opposite parameters additional for those who suppose you may want them.

You may wish to management the title and the folder the place the macro saves the file. If that’s the case, you will in all probability wish to add some error-handling code. As well as, the ws expression, which should reference a worksheet object is not crucial, and when not explicitly referenced, defaults to the lively sheet. Meaning, you need to go to the Stratification sheet to execute the macro. You might simply specify the worksheet utilizing a Dim ws As Worksheet line and outline it accordingly.

This text exhibits you tips on how to save the lively sheet as a PDF and I’ve supplied a easy macro that does the identical factor. You may, if wanted, add to that macro to specify the filename and the default folder. 

Additionally see

Source link