CODEWITHSUNDEEP
excelvba
DataNinja
DataNinja

Reputation: 67

How To Save Current Color Scheme in a variable and set a new workbook's color scheme to that variable

I develop Excel-based tools for my department and recently had a question I just can't put my finger on. I have working code that copies specific sheets within ActiveWorkbook, creating a new workbook with only those sheets. The issue I'm having is that the color theme resets when this happens. I have tried fixing using the below code but am getting a type mismatch error.

any help would be greatly appreciated.

Running Microsoft O365 - Excel 2016

Sub SomeSub()

dim wsColors as Variant

set wsColors = ThisWorkbook.Colors()

Thisworkbook.Worksheets(1).Copy

Activesheet.Colors() = ws.Colors

End Sub

Expected Results were that I would get the color scheme of the current workbook saved to the variant wsColors, then set the newly minted worksheet with the same color scheme.

I am only using a preset color scheme (Windows 2007 - 2010) as my desired endpoint so if there is another way to just refer to this as it is available in every workbook without having to take it from another, I am all ears.

SOLVED -

Was able to accomplish this with

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

Upvotes: 0

Views: 751

Answers (1)

Tin Bum
Tin Bum

Reputation: 1491

2 or 3 changes fixes it

Sub SomeSub()
  Dim wsColors As Variant

  wsColors = ThisWorkbook.Colors
  ThisWorkbook.Worksheets(1).Copy
  ActiveWorkbook.Colors() = wsColors
End Sub

Upvotes: 1

Related Questions