Chart color palette sync issue

[jmenche]

Howdy,

I have a spreadsheet that allows a user to save a few sheets to a new workbook and save it. However, when they open it up, all of the chart colors are different. I understand that our color palettes are different.

Is there something that I can do on my end to prevent this?

Thanks

Jeff

[p45cal]

see http://www.excelforum.com/excel-general/770553-excel-2010-import-color-scheme.html

now if you want code, we'll need more data; version(s) of excel, is it more than just the color palette, or could it be themes?, etc.

[jmenche]

Both of us are using 2010 or greater. I am not sure what a theme does and does not do. It works for me but not my client so I cannot replicate the error. Most of her colors come up wrong.

[p45cal]

a spreadsheet that allows a user to save a few sheets to a new workbook and save it
How is this done? Manually? With automation (code)? The ability to save a sheet as a new workbook is built in to Excel; so what is it that 'allows' this?
Will it be a matter of training people to do it or a matter of amending code? If the latter then give us the code you already have and we'll tweak.

[jmenche]

A few sheets get copied to a new workbook for the user to save. When I open the new workbook, the colors are fine but the client gets different colors when they save the new file.

[jmenche]

Here is the code that creates the new workbook. You'll see at the end that I try color palette fix that doesn't work. The problem becomes when the client runs this and opens the new workbook. All of the colors get changed. I was looking up templates. Is there maybe a way for me to create a template file of my colors and send that to the client?

Anywho, I greatly appreciate any help

Jeff



Sub SaveScenario()
Dim i, LastRow As Integer
Dim wks As Worksheet
Dim wkbSrc, wkbNew As Workbook
Dim shp As Shape
Dim cel, rngDeleteRows As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Create new workbook for scenario
Set wkbNew = Workbooks.Add
Set wkbSrc = ThisWorkbook

'Fill collection with sheets to be copied
For i = 1 To colMySheets.Count
colMySheets.Remove 1
Next i

colMySheets.Add ThisWorkbook.Sheets("Buyer Dist. - Category")
colMySheets.Add ThisWorkbook.Sheets("Buyer Dist. - Brands")
colMySheets.Add ThisWorkbook.Sheets("Purchase Dynamics - Dollars")
colMySheets.Add ThisWorkbook.Sheets("Purchase Dynamics - Units")
colMySheets.Add ThisWorkbook.Sheets("Purchase Dynamics - Volume")
colMySheets.Add ThisWorkbook.Sheets("Channel Dynamics")
colMySheets.Add ThisWorkbook.Sheets("Opportunity Calculator")

'Copy each sheet to new workbook
For Each wks In colMySheets
wks.Copy after:=wkbNew.Sheets(wkbNew.Sheets.Count)
Next

'Delete original empty sheets
For Each wks In wkbNew.Worksheets
If wks.Name Like "Sheet*" Then
wks.Delete
End If
Next

'Delete macro buttons and range value data
For Each wks In wkbNew.Worksheets
wks.Unprotect Password:="gfk"
wks.Activate
For Each shp In wks.Shapes
If shp.HasChart = msoTrue Then GoTo SkipMe
shp.Delete
SkipMe:
Next
wks.Cells.Copy
wks.Range("A1").PasteSpecial (xlPasteValues)
wks.Range("D5:D9").Validation.Delete
wks.Range("A1").Select
Next

'Fill collection with Purchase Dynamics tabs
For i = 1 To colMySheets2.Count
colMySheets2.Remove 1
Next i

colMySheets2.Add wkbNew.Sheets("Purchase Dynamics - Dollars")
colMySheets2.Add wkbNew.Sheets("Purchase Dynamics - Units")
colMySheets2.Add wkbNew.Sheets("Purchase Dynamics - Volume")

'Delete hidden rows of duplicate data
For Each wks In colMySheets2
wks.Activate
LastRow = Range("A1").End(xlDown).Row
For Each cel In Range("A1:A" & LastRow)
If cel.EntireRow.Hidden = True Then
If rngDeleteRows Is Nothing Then
Set rngDeleteRows = cel
Else
Set rngDeleteRows = Union(rngDeleteRows, cel)
End If
End If
Next
If Not rngDeleteRows Is Nothing Then
rngDeleteRows.EntireRow.Delete
End If
Set rngDeleteRows = Nothing
Next

'Copy color palette
For i = 1 To 56
wkbNew.Colors(i) = wkbSrc.Colors(i)
Next

wkbNew.Sheets(1).Activate

Call SaveAsName

ActiveSheet.Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[Paul_Hossler]

ActiveWorkbook.ResetColors


will reset the palette colors to their default values

[jmenche]

Thanks, Paul. Where do I put this in? (That's what she said!).

[Kenneth Hobs]

Where is up to you. Looks like you would want it after the Workbooks.Add line.

Please use Code tags and paste code between them. You can click the # icon which inserts this or type:
(code)(/code)
and change ()'s to []'s. Most all forums do code tags like that. That will take care of the smilie face issues as well in the code anyway.

[p45cal]

Both of us are using 2010 or greater. I am not sure what a theme does and does not do.Nor do I.
In the ribbon, on the Page Layout tab, in the Themes section (on the left usually) are you both using the same theme?
Try changing the theme and see if that affects the colurs of the chart.
Look at the answer beginning "My apologies for reopening this post" at http://superuser.com/questions/239869/cell-colors-change-when-copypaste-in-excel-2010
Other sites of interest may be:
http://stackoverflow.com/questions/4077660/excel-2007-vba-applytheme-addin
http://www.excelcampus.com/tools/excels-color-palette-compatibility-solution/