If you work with Excel, you undoubtedly have been asked to create summaries of your work. Sales reports, invoices, forecasts, schedules, you name it. You know what all these documents have in common? They look great in PDF form. You know what else? Management loves PDFs!

Convert Excel to PDF

In this tutorial, I’ll show you how to use Excel VBA to convert all kinds of Excel objects to PDFs: I’m going to present each of the macros with some commentary. That way, you’ll be able to quickly find them, copy them to your VBA editor and use them. When you run any of these macros, a Save As dialog box will appear asking you where you want to save your PDF. The default name includes the date and timestamp when you executed the macro in yyyymmdd_hhmmss format.

Let’s get right to it. Here are the macros:

This one is my personal favorite. This macro will convert the cells you actively have selected into a PDF. If you only have one cell selected, the VBA macro is smart enough to realize that you probably don’t want to convert just one cell so it asks you to select the range you want to convert:

Once you select the range and click OK, it will show a dialog box where you can select where you want to save the PDF. It will automatically pick the date and time from your system’s clock and add it to the file name.

A lot of Excel power users store their data into organized tables. As a matter of fact, Sumit Bansal himself likes Excel tables so much he calls them a hidden treasure in Excel. This macro prints a table of your choosing to a PDF. When you run the macro, it will prompt you for the name of the table you want to save.

Once you enter the name of the table and click OK, it will show a dialog box where you can select where you want to save the PDF. It will automatically pick the date and time from your system’s clock and add it to the file name.

If your spreadsheet has several tables and you need to save each one to a separate PDF, you can run this VBA code. When you run this macro, a dialog box will appear asking you to select the folder where you want to save your PDFs.

Once you pick your folder, the macro will save each table to a PDF with the table name conveniently appearing in the title of the PDF.

I don’t know about you, but in my line of work, we have to retain PDF copies of almost all our spreadsheets. We append the PDF copies of our spreadsheets to our design calculations. These PDFs used to be converted to microfiche and vaulted for long-term retention. You know, in case the apocalypse happened. If you find yourself in a similar situation, it’s nice to be able to automatically convert all the sheets in your spreadsheet into one PDF. Here’s a VBA macro that will do just that:

This macro converts all your Chart Sheets into one PDF – but not your chart objects! By chart sheets, I mean the charts that have their own tab in your list of spreadsheet tabs:

This macro saves all your normal charts – chart objects – into a single PDF. Regardless of which worksheet your chart is on, it will be grabbed and saved to a PDF. There will only be one chart per page in the final PDF. If you have any questions, drop them in the comments section, where Ryan and I will be waiting there for you.

How to Embed a PDF File in an Excel Worksheet. Excel VBA Loops: For Next, Do While, Do Until, For Each (with Examples). How to Record a Macro in Excel How to Combine Multiple Workbooks into One Excel Workbook. How to Run a Macro in Excel. How to Create and Use an Excel Add-in. Excel VBA Error Handling Split Each Excel Sheet Into Separate Files How to Import XML File into Excel | Convert XML to Excel

About the author: Ryan Wells is a Nuclear Engineer and professional VBA Developer. He publishes his easy to understand Excel VBA tutorials to help others write better macros. In addition to teaching VBA, Ryan is the lead developer of several Excel add-ins. You can find his tutorials on WellsR.com. 2. automatically pick name from cell value & save in predefine location(d:/jobcard/*.pdf thanks for help in advance I need to save this as PDF file as wellafter printing with all these 4 Sheet in a single PDF file with continuous 4 pages each having cell value(L1) as defined by Array. Any Advice in this regard shall be highly appreciated. Syed. Sub PrintINVOICEQuadtriplicate() Dim i As Integer Dim VList As Variant VList = Array(“ORIGINAL FOR RECIPIENT”, “DUPLICATE FOR TRANSPORTER”, “TRIPLICATE FOR SELLER”, “”) For i = LBound(VList) To UBound(VList) Range(“L1”) = VList(i) ActiveSheet.PrintOut Next End Sub Is there a way to have a predetermined set of cells selected already as opposed to being prompted? and also have all that fit in one page as either landscape or portrait. Thank you in Advance Sub GeneratePDF_Click() Dim thisWb As Workbook: Set thisWb = ThisWorkbook Dim thisWs As Worksheet: Set thisWs = thisWb.Worksheets(“Invoice”) ‘replace with relevant name Dim newBook As Workbook Dim newws As Worksheet Dim pathToNewWb As String Dim uKeys(1 To 3) As Variant Dim currentPath, columnWithKey, numCols, numRows, dataStartRow, uKey, columnKeyName Dim i As Integer Dim rng As Range, Option1 As Range Dim Filename As String Dim dropdown1 As DropDown Dim C As String Filename = “Test” Dim wksAllSheets As Variant Dim wksSheet1 As Worksheet Dim shtAry() ‘nobody likes flickering screens Application.ScreenUpdating = False ‘remove any filter applied to the data thisWs.AutoFilterMode = False ‘get the path of the workbook folder currentPath = Application.ThisWorkbook.Path ‘Set the stage ‘###Hardcode### ‘columnKeyName = “Facility” ‘name of the column with the facility values dataStartRow = 7 ‘this is a pure guess, correct as relevenat. Use the header row index pathToNewWb = currentPath & “/Business Plans.xlsx” ‘ where to put the new excel, if you want a saveas prompt you should google “Application.FileDialog(msoFileDialogSaveAs)” ‘dropdown1 = “F7” i = 1 Set rng = Evaluate(Range(“F7”).Validation.Formula1) ‘Set rng = Range(“F7”) For Each Option1 In rng uKeys(i) = Option1.Value i = i + 1 Next Option1 For i = LBound(uKeys) To UBound(uKeys) Debug.Print uKeys(i) Next i ‘uKeys = Range(“C2:C5”).Value ‘###Hardcode End### ‘columnWithKey = thisWs.Range(dataStartRow & “:” & dataStartRow).Find(what:=columnKeyName, LookIn:=xlFormulas).Value numCols = thisWs.UsedRange.Columns.Count ‘extract the index of the last used row in the worksheet numRows = thisWs.UsedRange.Rows.Count ‘create the new workbook Set newBook = Workbooks.Add ‘loop the facilities, and do the work For Each uKey In uKeys ‘Filter the keys column for a unique key ‘thisWs.Range(thisWs.Cells(7, 6), thisWs.Cells(numRows, numCols)).AutoFilter field:=”F7″, Criteria1:=uKey ‘thisWs.Range(thisWs.Cells(7, 6)).Select ‘Range(“F7”).Value = Range(Range(“F7”).Validation.Formula1)(2).Value Cells(7, 6) = uKey ‘copy the sheet thisWs.UsedRange.Copy ‘Create a new ws for the facility, and paste as values Set newws = newBook.Worksheets.Add ActiveWindow.Zoom = 90 With newws ActiveWindow.Zoom = 90 .Name = uKey ‘I assume the name of the facility is the relevant sheet name ActiveWindow.Zoom = 90 .Range(“B1:F25”).PasteSpecial xlPasteValues .Range(“B1:F25”).PasteSpecial Paste:=xlPasteColumnWidths .Range(“B1:F25”).PasteSpecial Paste:=xlPasteFormats ‘.Range(“B1:F27”).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats ‘.Range(“B1:F27”).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows(3).Select Selection.RowHeight = 43.5 .Rows(4).Select Selection.RowHeight = 69 .Rows(5).Select Selection.RowHeight = 33 .Rows(6).Select Selection.RowHeight = 24.75 .Rows(7).Select Selection.RowHeight = 27.75 .Rows(9).Select Selection.RowHeight = 24.75 .Rows(12).Select Selection.RowHeight = 19.5 .Rows(13).Select Selection.RowHeight = 49.5 .Rows(14).Select Selection.RowHeight = 34 .Rows(15).Select Selection.RowHeight = 34 .Rows(16).Select Selection.RowHeight = 34 .Rows(17).Select Selection.RowHeight = 34 .Rows(22).Select Selection.RowHeight = 33 .Rows(23).Select Selection.RowHeight = 45.75 .Rows(24).Select Selection.RowHeight = 14.75 .Rows(25).Select Selection.RowHeight = 15.75 With ActiveSheet.PageSetup .LeftMargin = Application.InchesToPoints(0.3) .RightMargin = Application.InchesToPoints(0.3) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) ‘.Orientation = xlLandscape .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With End With ‘remove autofilter (paranoid parrot) thisWs.AutoFilterMode = False Next uKey ‘save the new workbook newBook.SaveAs pathToNewWb With ActiveSheet.PageSetup ReDim shtAry(3) ‘ this is an array of length 2 For i = 1 To 4 shtAry(i – 1) = Sheets(i).Name Debug.Print Sheets(i).Name Next i Sheets(shtAry).Select Debug.Print ThisWorkbook.Path & “” .LeftMargin = Application.InchesToPoints(0.3) .RightMargin = Application.InchesToPoints(0.3) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) ‘.Orientation = xlLandscape .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & “/” & Filename & “.pdf”, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False newBook.Close End Sub numCols = thisWs.UsedRange.Columns.Count ‘extract the index of the last used row in the worksheet numRows = thisWs.UsedRange.Rows.Count ‘create the new workbook Set newBook = Workbooks.Add ‘loop the facilities, and do the work For Each uKey In uKeys ‘Filter the keys column for a unique key ‘thisWs.Range(thisWs.Cells(7, 6), thisWs.Cells(numRows, numCols)).AutoFilter field:=”F7″, Criteria1:=uKey ‘thisWs.Range(thisWs.Cells(7, 6)).Select ‘Range(“F7”).Value = Range(Range(“F7”).Validation.Formula1)(2).Value Cells(7, 6) = uKey ‘copy the sheet thisWs.UsedRange.Copy ‘Create a new ws for the facility, and paste as values Set newws = newBook.Worksheets.Add ActiveWindow.Zoom = 90 With newws ActiveWindow.Zoom = 90 .Name = uKey ‘I assume the name of the facility is the relevant sheet name ActiveWindow.Zoom = 90 .Range(“B1:F25”).PasteSpecial xlPasteValues .Range(“B1:F25”).PasteSpecial Paste:=xlPasteColumnWidths .Range(“B1:F25”).PasteSpecial Paste:=xlPasteFormats ‘.Range(“B1:F27”).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats ‘.Range(“B1:F27”).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Rows(3).Select Selection.RowHeight = 36 .Rows(4).Select Selection.RowHeight = 69 .Rows(5).Select Selection.RowHeight = 33 .Rows(6).Select Selection.RowHeight = 24.75 .Rows(9).Select Selection.RowHeight = 24.75 .Rows(10).Select Selection.RowHeight = 18 .Rows(11).Select Selection.RowHeight = 18 .Rows(12).Select Selection.RowHeight = 19.5 .Rows(13).Select Selection.RowHeight = 49.5 .Rows(14).Select Selection.RowHeight = 34 .Rows(15).Select Selection.RowHeight = 34 .Rows(16).Select Selection.RowHeight = 34 .Rows(17).Select Selection.RowHeight = 34 .Rows(22).Select Selection.RowHeight = 33 .Rows(23).Select Selection.RowHeight = 45.75 .Rows(24).Select Selection.RowHeight = 14.75 .Rows(25).Select Selection.RowHeight = 15.75 With ActiveSheet.PageSetup .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1.5) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) ‘.Orientation = xlLandscape .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With End With ‘remove autofilter (paranoid parrot) thisWs.AutoFilterMode = False Next uKey ‘save the new workbook newBook.SaveAs pathToNewWb With ActiveSheet.PageSetup ReDim shtAry(3) ‘ this is an array of length 2 For i = 1 To 4 shtAry(i – 1) = Sheets(i).Name Debug.Print Sheets(i).Name Next i Sheets(shtAry).Select Debug.Print ThisWorkbook.Path & “” .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1.5) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) ‘.Orientation = xlLandscape .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & “/” & Filename & “.pdf”, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False newBook.Close End Sub Sub GeneratePDF_Click() Dim thisWb As Workbook: Set thisWb = ThisWorkbook Dim thisWs As Worksheet: Set thisWs = thisWb.Worksheets(“Invoice”) ‘replace with relevant name Dim newBook As Workbook Dim newws As Worksheet Dim pathToNewWb As String Dim uKeys(1 To 3) As Variant Dim currentPath, columnWithKey, numCols, numRows, dataStartRow, uKey, columnKeyName Dim i As Integer Dim rng As Range, Option1 As Range Dim Filename As String Dim dropdown1 As DropDown Dim C As String Filename = “Test” Dim wksAllSheets As Variant Dim wksSheet1 As Worksheet Dim shtAry() ‘nobody likes flickering screens Application.ScreenUpdating = False ‘remove any filter applied to the data thisWs.AutoFilterMode = False ‘get the path of the workbook folder currentPath = Application.ThisWorkbook.Path ‘Set the stage ‘###Hardcode### ‘columnKeyName = “Facility” ‘name of the column with the facility values dataStartRow = 7 ‘this is a pure guess, correct as relevenat. Use the header row index pathToNewWb = currentPath & “/Business Plans.xlsx” ‘ where to put the new excel, if you want a saveas prompt you should google “Application.FileDialog(msoFileDialogSaveAs)” ‘dropdown1 = “F7” i = 1 Set rng = Evaluate(Range(“F7”).Validation.Formula1) ‘Set rng = Range(“F7”) For Each Option1 In rng uKeys(i) = Option1.Value i = i + 1 Next Option1 For i = LBound(uKeys) To UBound(uKeys) Debug.Print uKeys(i) Next i Dim shtAry() ‘nobody likes flickering screens Application.ScreenUpdating = False ‘remove any filter applied to the data thisWs.AutoFilterMode = False ‘get the path of the workbook folder currentPath = Application.ThisWorkbook.Path ‘Set the stage ‘###Hardcode### columnKeyName = “Facility” ‘name of the column with the facility values dataStartRow = 1 ‘this is a pure guess, correct as relevenat. Use the header row index pathToNewWb = currentPath & “/Business Plans.xlsx” ‘ where to put the new excel, if you want a saveas prompt you should google “Application.FileDialog(msoFileDialogSaveAs)” i = 1 Set rng = Range(“C2:C5”) For Each cel In rng uKeys(i) = cel.Value i = i + 1 Next cel For i = LBound(uKeys) To UBound(uKeys) Debug.Print uKeys(i) Next i ‘extract the index of the last used row in the worksheet numRows = thisWs.UsedRange.Rows.Count ‘create the new workbook Set newBook = Workbooks.Add ‘loop the facilities, and do the work For Each uKey In uKeys ‘Filter the keys column for a unique key thisWs.Range(thisWs.Cells(dataStartRow, 1), thisWs.Cells(numRows, numCols)).AutoFilter field:=columnWithKey, Criteria1:=uKey ‘copy the sheet thisWs.UsedRange.Copy ‘Create a new ws for the facility, and paste as values Set newws = newBook.Worksheets.Add With newws .Name = uKey ‘I assume the name of the facility is the relevant sheet name .Range(“A1”).PasteSpecial xlPasteValues End With ‘remove autofilter (paranoid parrot) thisWs.AutoFilterMode = False Next uKey ‘save the new workbook newBook.SaveAs pathToNewWb I’ve tried to run this by defining “xPages” as the total count of pages (and then going for xpages -1 – 1) but am unable to run this within the settings for the .pdf export… Is this possible? Second workbook has a link to the first one so if I make any changes, it automatically updates with the new values. In the second workbook, I simply enter order code and quantities and it fills the invoice template. For each page, there are 25 entries. So if the invoice has more than 25 items, it automatically goes to second page. That way, I made the design up to 10 pages. Also in this workbook, in another sheet, there is the sales contract, which is automatically filled again according to the date, customer id, and invoice number. What I want to ask you is, I can make this program in Excel, too. Can you offer a VBA code, which will understand how many pages of the invoice is filled and convert to PDF accordingly along with adding the Sales Contract sheet automatically ?