Copy whole worksheet with keep COLOR format

Forestq

Active Member
Joined
May 9, 2010
Messages
482
Hi,

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

Code:
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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....

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

Forestq

Active Member
Joined
May 9, 2010
Messages
482
thank you Jerry, I tested and it works!

Also, I tried to do as below and orks:
Code:
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.
 

Forum statistics

Threads
1,085,307
Messages
5,382,857
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top