Solved: Moving or Copying Worksheets changes theme colour

[Wolfman1]

I have done some troubleshooting with colour change when moving/copying sheets to new workbooks that hasn't been documented very well online, for which a solution is yet to be found, and my findings are as follows:

Let's say that we are using the "Paste Special - All using source theme" option, only your data and formatting from the original worksheet would be retained, floating objects would not be copied over. This option will only work when there are no floating objects (charts, diagrams, shapes) in that worksheet. Selecting all from the worksheet, and pasting all in new workbook would paste floating objects, however colour change issue will persist.

To have all contents pertaining to a sheet (including floating objects) one would have to move/copy the sheet to the new/destination workbook. Upon doing this, all colours would change to a different theme, including the colours of charts. This is the case even when colour pallets of both workbooks are the same, resetting the colours in Options ---> Save ---> Colours ---> reset will not make any change.

Eg. I have a file that originates from an Office 2010 or 2007 platform, when copying/moving the sheet to a new workbook the theme colours change to different shades of yellow and grey (from pale red, blue, green and purple). I am now using Office 365 on Win8.

This problem is not present when you are using workbooks originally created in Office 365 (no, saving as in 365 does not resolve this either), but on files created with previous versions of office the issue is unresolved when used in a later version of Office.

THE SOLUTION:

Page Layout ---> Colours ---> Office 2007-2010 (or any of the other colours available)



And for VBA:

ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
        "C:\Program Files\Microsoft Office 15\Root\Document Themes 15\Theme Colors\Office 2007 - 2010.xml" _
        )

I hope this helps somebody else out as this has baffled me for days. Happy coding.

 

[barefeetequestrian]

Thank you, I used it today!! Would have never guessed.

 

[Ashlis]

Thanks a lot!! I've been baffled with the color change for one year! Glad to have a solution now.

 

[mikeymay]

Very useful to know but at what point do you place the code?

Prior to creating the new file with the copied sheets or in the new file?


Thanks

 

[mikeymay]

I've just implemented this into my file and it changes all the colours in the file.

I have some very specific color fills in cells and graphs and these all get changed when I load the themes file.

I do need to retain what is in the original file as the RGB codes/themes in the themes file don't match the RGB codes I need to use.


Thanks

 

[karthikcoep]

Thank you...

 

[UWMmakow]

Thank you.