Copying Sheets to a new workbook switches the fill color to the new 2013 pallette.

[Rob Lehman]

I am copying selected sheets from a workbook to a new workbook so that I can publish it with values only. when I do this in Excel 2013 (the workbook was created in a prior version of Excel), it automatically adjusts my cell fill colors to the new 2013 theme colors. I want it to keep my old theme colors. I am copying the sheets via a macro, but the same thing happens when I manually copy the sheet out to a new workbook. Any ideas?? Thanks! Rob

 

[JLeno]

You can retreive the colors from the old workbook using this code:

Dim i As Integer

For i =1 To 56
wbNew.Colors(i)=wbOld.Colors(i)
Next i

set the wbNew and wbOld to the respective workbooks

 

[Rob Lehman]

So I tried this...

    Dim wbNew As Workbook
    Dim wbOld As Workbook
    Dim i As Integer
    
    Set wbOld = ActiveWorkbook()
        
    ' Copy the desired sheets
    Sheets(Array("FS1--Comparative", "IS - Other Inc-Exp Sched", "FS2--Monthly", "FS3--People", "Budget Mgr Summary", "Accruals")).Copy
    
    Set wbNew = ActiveWorkbook()
    For i = 1 To 56
        wbNew.Colors(i) = wbOld.Colors(i)
    Next I

and it didn't change anything. Didn't get any errors, but I think maybe I'm not setting wbOld and wbNew appropriately? I assumed, since the new workbook is created and activated by the Sheets.Copy line that ActiveWorkbook() would work in both places?

 

[JLeno]

That's funny, for me it does work...

It's important that the new workbook is active at the 'Set wbNew' line.

If that's not it, I have no clue, sorry

 

[Rob Lehman]

Well...thanks much for your quick reply. I did some debug.print statements and wbNew and wbOld are identical both before and after the statement. Seems like a glitch in Excel that it would change the colors in the first place. I may just have to deal with the new colors.