Copy whole worksheet with keep COLOR format

[Forestq]

Hi,

I tried to copy selected worksheet to new WorkBook. But during the operation CELLs/COLUMN/ROW COLOR have been changed :/

For Each ws In wb.Sheets
        next_ws = ws.Name
        
        If dict_sheet.exists(next_ws) Then

            If next_ws = "Sam" Then
                Set NewBook = Workbooks.Add
                ws.Copy Before:=NewBook.Sheets(1)
                    NewBook.Worksheets("Sam").Cells.Select
                    Selection.Copy
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    Range("A1").Select
            Else if....
            etc....
        End if
      End if
Next

Do you know why or how to resolve the issue?

 

[Jerry Sullivan]

Hi Forestq,

If your source workbook is using theme colors (a pallet) that is different than the one in your target workbook (or new workbook template), then the displayed colors will change.

You can add a step to load the same pallet in the new workbook.

For example to load the "Office 2007 - 2010" pallet....

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

 

[Forestq]

thank you Jerry, I tested and it works!

Also, I tried to do as below and orks:

Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

But I need to first copy as paste as value, then above code (but all function will be write), so then once again I need to paste as value.

Your code is much more transparent.