MS Excel do not copy the color scheme automatically

[Ariff_Chowdhury]

I am using MS Excel 2010, My Company uses a set of standard color scheme for MS Excel.I gave it a name (companycolor). I have a template contains that color scheme and a macro in it to perform tasks. When I press macro button it makes a copy of activesheet,protect it and email it to intended recipient.Problem is that when macro makes a copy of activesheet it doesn't copy the color scheme that template have i mean with the my company color scheme (companycolor) due to which all cells color, color of charts and shapes get disturbed and changed according to Excel default color scheme which seems very odd. Do you have any way forward to overcome this issue or any suggestion in this regards? Thank you!
Here is the screen shot that shows the company color scheme in the original file and after copying the file for sending email how the color scheme changed to default
Edited.jpg

[Ariff_Chowdhury]

BUMP - need a reply!

[martindwilson]

i think all the rest would have to be set to your colour scheme by choice,i wouldnt be happy to receive a file that automatically changed mine.

[Ariff_Chowdhury]

I dont exactly got your point!
I have excel workbbook named (Project Status) - contains 50-55 worksheets that we use for our projects,this file containts the company color theme.
each worksheet from the Project Status is email to concerned people only daily basis - The emailing task is automated through VBA.

How do we do that or say we do daily with file..

1 - Open the excel file (Project Status) - perform updating related tasks this is manual process.
2 - Press email button.
3 - The vba code first makes a copy of active sheet into new workbook with file name defined as a cell value in the vba code,then protect the worksheet and email it relevant person
PROBLEM :

When vba code makes copy of active sheet into new workbook, the color scheme got changed into the default excel color theme (As shown in the png file earlier,side by side, left one show the template with company color theme and the right one shows the color theme that has been changed after making copy).

I have query that can we keep maintained the color theme of project status' file into the newly copy file through vba code ?

(Manually this is performed in Excel 2013 in the Page Layout option there is option "Colors" we have to change the "Office" color theme into our custom color theme named "mycompanytheme".if we would have to work with single worksheet then no issue but there are approx 50-55 worksheets that we have to email to relevant peoples).


Thank you!

[Ariff_Chowdhury]

BUMP -need a reply!

[:) Sixthsense :)]

Two questions from my end:-

1. What happens if you open the workbook in some other computer whether it will have the same Company Formatting?
2. If so why don't you try to DUPLICATE THE WORKBOOK to multiple and delete the unwanted sheets and send it to the customer.

[Ariff_Chowdhury]

Here is the answers

1. 1 - If I open the template workbook in some other computer,whether it will have same company formatting OR not the theme color remains same not get disturbed.
2. 2 - It is roundabout way of resolving the issue, there are 50-60 worksheets need to be emailed daily several time (whenver we changes in the data).And sometimes the number of worksheets increased as well.

[Ariff_Chowdhury]

Here is the vba code that makes copy of active worksheet from active workbook into a new workbook, protect it and email it.

Please Login or Register  to view this content.

So far I have reached to the conclusion that in any way if we get able to incorporate this below xml code into the above vba code then we can get the desired result. But i dont know how.

Please Login or Register  to view this content.

[:) Sixthsense :)]

I just tried to give some ideas to you and anyhow you have fixed it and now you are aware what to do next... But my knowledge about vba is limited so I can not able to help you in incorporating the xml in your code.

I believe someone will help you to achieve this

[Ariff_Chowdhury]

BUMP - need a reply!!! please!

[Ariff_Chowdhury]

BUMP - need a reply!!! please!

[Ariff_Chowdhury]

Finally I found a way to get it worked!

Describing solution so others can get help from this!
Here is the conclusion and it worked!
First of all by giving convenient path to the this vba code,paste it on the file that has any specific color scheme theme.

Please Login or Register  to view this content.

The above code will generate an xml file in your specified path.

Then, paste the below line of code giving the same path where your xml file resided, above your "email sending" code.

Please Login or Register  to view this content.

Now it will it copy the theme in a new workbook.

By default the theme configuration reside on

Please Login or Register  to view this content.

Thank you all for your support too!