Conditional Formatting changes from XLSM to XLSX when I use VBA code to save a new file

[Stevep4]

I have the below code that takes two worksheets in an xlsm workbook and it saves them as an xlsx workbook. The problem is that when the two sheets are saved into an new xlsx workbook the conditional formatting colors change. Is there a way to keep the formatting from XLSM to XLSX?

Sub SaveCopyAs_Without_MacrosStore()
ThisFile = Sheets("Summary").Range("d11:d11").Value
Sheets(Array("LW-MTD-YTD", "By Week")).Copy
ActiveWorkbook.SaveAs Filename:="S:\Merch Analysis\ALL REPORTS\Monday Reporting\SenSource\Archive\" & ThisFile & ".xlsx", FileFormat:=51
Set Wkb = Workbooks.Open("S:\Merch Analysis\ALL REPORTS\Monday Reporting\SenSource\Archive\" & ThisFile & ".xlsx")
Wkb.Close Savechanges:=True
End Sub

 

[JerrExcel]

Hi Stevep4,

Are you still looking for solution?

There might be a few color RGB in the Theme need to be copied. I added this loop before closing the new workbook. Refer below.

Try this code:

Sub SaveCopyAs_Without_MacrosStore()
ThisFile = Sheets("Summary").Range("d11:d11").Value
Sheets(Array("LW-MTD-YTD", "By Week")).Copy
ActiveWorkbook.SaveAs Filename:="S:\Merch Analysis\ALL REPORTS\Monday Reporting\SenSource\Archive\" & ThisFile & ".xlsx", FileFormat:=51
Set Wkb = Workbooks.Open("S:\Merch Analysis\ALL REPORTS\Monday Reporting\SenSource\Archive\" & ThisFile & ".xlsx")

For i = 1 To ThisWorkbook.Theme.ThemeColorScheme.Count
Wkb.Theme.ThemeColorScheme.Colors(i).RGB = ThisWorkbook.Theme.ThemeColorScheme.Colors(i).RGB
Next i

Wkb.Close Savechanges:=True
End Sub