Removing Conditional Formatting When Printing to PDF

[DC2DC]

My Spreadsheet that has various colours on it based on Conditional Formatting all in Col F and I use the code below to extract and name the sheet as a PDF, this runs off a Macro Button 'Print Colour' which works fine.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A10").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True


I also need the ability to print the same sheet in Black/White via a separate Macro Button, but I cannot figure out how to adapt the Code above to remove the Conditional Formatting, print to PDF and then reapply the original Conditional Formatting.

Any ideas please - thanks

[Kenneth Hobs]

Welcome to the forum!

You could mirror the sheet to another less the formatting not needed or create it from scratch, and then fill with values, not formatting.

[DC2DC]

Thanks for your reply Kenneth.

I did think about that but the sheet is filled out by people and may have more or less lines inserted into it, the users are not the most 'savvy' in the world so I wanted to make it simple for them, so all they had to do was click a button for a colour copy or a B/W copy

[Kenneth Hobs]

In this method, I created a temp workbook.


Sub MakePDFs()
Dim wb, fn As String

With Sheet1
fn = .Range("A10").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn & "_Color" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End With

Set wb = Workbooks.Add(xlWBATWorksheet)

With wb.Worksheets(1)
Sheet1.UsedRange.Copy
.Range("A1").PasteSpecial xlPasteValues
.Range("A1").PasteSpecial xlPasteColumnWidths
.Range("A1").PasteSpecial xlPasteFormats
.UsedRange.FormatConditions.Delete
.UsedRange.Font.Color = RGB(0, 0, 0) 'Black

.UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn & "_B&W" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
wb.Close False
End With

Application.CutCopyMode = False
End Sub

[DC2DC]

Thanks Kenneth

That worked... but it lost all the header and footer information when the temp workbook is created along with the page formatting. When two PDFs were created the "_color" was correct but the "_B&W" was 51 pages with no Header or footer information.. but it was in B&W which was good!

[Kenneth Hobs]

Maybe try:

Sub MakePDFs2()
Dim wb, fn As String

With Worksheets("Sheet1")
fn = .Range("A10").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn & "_Color" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End With

Set wb = Workbooks.Add(xlWBATWorksheet)

ThisWorkbook.Worksheets("Sheet1").Copy after:=wb.Worksheets(1)
ActiveSheet.UsedRange.FormatConditions.Delete
ActiveSheet.UsedRange.Font.Color = RGB(0, 0, 0) 'Black

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn & "_B&W" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
wb.Close False
End Sub