Conditional Formatting lost on Copying cells

[GAJITCS]

I have a table that is subjected to conditional formatting and works exactly as required.

I have the need to copy that table and save it in it's own workbook and this is where the problem is.

The code used is as follows:

Dim Pth As String

Pth = ThisWorkbook.Path
Sheets("Export To Excel").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Pth & "" & ("Routing Map") & " " & Format(Now, "yyyy-dd-mm hh_mm_ss") & ".xlsx", 51
Application.DisplayAlerts = True
ActiveWorkbook.Close False

Stepping into the code, all is well until after the third line - Sheets("Export To Excel").Copy

There after, when the copied data arrives in a new workbook, the colours applied with the conditional formatting are completely different to those present when the "Copy" takes place.

Ignoring the VBA, the same thing happens when I just try to copy and paste the table from the source workbook into a new workbook. If I copy from source worksheet to a new worksheet in the same workbook, it's all okay.

Looks like the issue is copying between workbooks and the links to the conditions of the formatting.

I don't know where to start looking.

 

[Yongle]

Possible workaround (not tested)
- Save the workbook under a different name
- delete what you don't want