VBA: How to Automate PDF Export of Filtered Data Without Losing Formatting?
I'm maintaining legacy code that I'm trying to automate the process of exporting filtered data from an Excel worksheet to a PDF file using VBA. The challenge arises when I apply filters to my data – the exported PDF does not retain the formatting (like cell colors and font styles) that I see in the Excel sheet. I'm using Excel 2016 and have tried the following code to filter and export: ```vba Sub ExportFilteredDataToPDF() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("DataSheet") ' Apply filter to a specific column ws.Range("A1:D100").AutoFilter Field:=1, Criteria1:=">100" ' Attempt to export the visible range to PDF On Error Resume Next ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Outputs\FilteredData.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True On Error GoTo 0 ' Remove the filter ws.AutoFilterMode = False End Sub ``` When I run this code, the PDF exports without the desired formatting. I also attempted to copy the visible cells to a new temporary worksheet and then export it: ```vba Sub ExportFormattedDataToPDF() Dim ws As Worksheet, tempWs As Worksheet Set ws = ThisWorkbook.Sheets("DataSheet") Set tempWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' Copy the visible cells to the new sheet ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy tempWs.Range("A1").PasteSpecial Paste:=xlPasteAll ' Export the temporary sheet to PDF tempWs.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Outputs\FormattedData.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True ' Clean up Application.DisplayAlerts = False tempWs.Delete Application.DisplayAlerts = True End Sub ``` However, this approach still results in a PDF with lost cell formatting. I’ve also checked the print area settings, but it seems that the formatting is still not being preserved. Is there a specific way to ensure that the formatting of cells is retained when exporting to PDF, especially with filtered data? Any insights or best practices would be greatly appreciated! For reference, this is a production microservice. This issue appeared after updating to Vba 3.9. Any help would be greatly appreciated!