Export Excel Worksheet Ranges as PDF and XLSX Using VBA
Question details
The user needs to write a VBA macro that exports specific column ranges from every worksheet into individual PDF and Excel (XLSX) files, using the worksheet names as the resulting filenames.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the process of extracting specific data from a large workbook and distributing it into individual, cleanly formatted files (both PDF and XLSX) for each worksheet.
- Observed behavior
- A VBA script is required to loop through worksheets, set print areas for PDF export, copy sheets to new workbooks, strip formulas to values, remove shapes, and save the files.
Before running the macro, ensure you have enabled the Developer tab in your spreadsheet software and saved your current workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.
Use a VBA Macro to Export Worksheets as PDF and XLSX
Implement a VBA script that loops through all worksheets, defines the print area for PDF export, and copies the specified columns into new Excel workbooks.
This macro will iterate through every worksheet in your active workbook. For each sheet, it will set the designated columns as the print area to generate a PDF. It will then copy the sheet, remove unwanted columns, convert formulas to static values, and save it as a new XLSX file named after the original sheet.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu, then select 'Module' to create a new blank script window.
Write a script utilizing 'For Each ws In ActiveWorkbook.Worksheets'. Inside the loop, use 'ws.PageSetup.PrintArea' to set the columns for PDF export, followed by 'ws.ExportAsFixedFormat Type:=xlTypePDF'.
Still inside the loop, use 'ws.Copy' to create a new workbook. In the new workbook, delete unneeded columns, select the remaining range to copy and 'PasteSpecial xlPasteValues' to remove formulas, and finally use 'ActiveWorkbook.SaveAs' to save it as an XLSX file.
Close the VBA editor, press Alt + F8, select your new macro from the list, and click 'Run' to execute the batch export.
Automate Your Tasks with WPS Spreadsheet Macros
WPS Spreadsheet offers robust support for VBA macros, allowing you to run complex automation scripts like exporting multiple sheets to PDF and XLSX seamlessly.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data and VBA code you want to execute.
- 2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon. If it is not visible, you can easily enable it in the WPS settings.
- 3. Run the Export Macro: Click on 'Macros', select your export script from the dialog box, and click 'Run' to automatically generate your PDF and XLSX files.
Frequently Asked Questions
Why is my macro not running to export the files?
Macros are often disabled by default for security reasons. You need to go to the Trust Center settings (or Macro Security settings in WPS) and enable macros, or save your file as a Macro-Enabled Workbook (.xlsm).
How do I specify a save folder for the exported files?
In your VBA code, you can define a file path string (e.g., SavePath = "C:\Users\Username\Desktop\Export\") and concatenate it with the worksheet name when calling the SaveAs or ExportAsFixedFormat methods.
Can I export only visible sheets instead of all worksheets?
Yes. You can add an 'If' statement inside your worksheet loop to check the sheet's visibility property (e.g., 'If ws.Visible = xlSheetVisible Then') before executing the export and save commands.
How do I convert formulas to values during the XLSX export?
Before saving the newly copied workbook, select the used range and copy it, then use PasteSpecial xlPasteValues to replace formulas with their calculated results. This ensures no broken references in the exported file.
