title: MS Excel do not copy the color theme automatically
body: <blockquote>
    <p><strong>I am using MS Excel 2010
    My Company uses a set of standard color scheme / theme for MS Excel 2010 .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 into a new workbook it doesn't copy the color scheme / theme 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</strong></p>
  </blockquote>
  
  <p><strong>Here is the link of <a href="http://www.excelforum.com/attachment.php?attachmentid=251815&amp;d=1374460039" rel="nofollow">Snap Shot!</a>, help you to understand my problem better</strong>
  *<em>>>Here is the vba code that makes copy of active worksheet from active workbook into a new workbook, protect it and email it.</em>***</p>
  
  <pre><code>Private Sub CommandButton2_Click()
  
  Dim FileExtStr As String
  Dim FileFormatNum As Long
  Dim Sourcewb As Workbook
  Dim Destwb As Workbook
  Dim TempFilePath As String
  Dim TempFileName As String
  Dim OutApp As Object
  Dim OutMail As Object
  
  If (Range("AQ5") &lt;&gt; "") Or (Range("AQ6") &lt;&gt; "") Then
  Range("A5").Select
  
  With Application
   .ScreenUpdating = False
   .EnableEvents = False
  End With
  
  Set Sourcewb = ActiveWorkbook
  Application.ScreenUpdating = False
  
  ActiveSheet.Copy
  
  Range("A14").ClearContents
  ActiveSheet.Protect Password:="1234567890"
  Set Destwb = ActiveWorkbook
  
  With Destwb
  If Val(Application.Version) &lt; 12 Then
      FileExtStr = ".xls": FileFormatNum = -4143
  Else
      If Sourcewb.Name = .Name Then
          With Application
              .ScreenUpdating = True
              .EnableEvents = True
          End With
          MsgBox "Your answer is NO in the security dialog"
          Exit Sub
      Else
          Select Case Sourcewb.FileFormat
          Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
          Case 52:
              If .HasVBProject Then
                  FileExtStr = ".xlsm": FileFormatNum = 52
              Else
                  FileExtStr = ".xlsx": FileFormatNum = 51
              End If
          Case 56: FileExtStr = ".xls": FileFormatNum = 56
          Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
          End Select
      End If
   End If
   End With
  
   TempFilePath = Environ$("temp") &amp; "\"
   TempFileName = "DI Status for " &amp; Range("A17") &amp; " Dated " &amp; Format(Now, "dd-mmm-yy h-mm-ss")
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  
  With Destwb
  .SaveAs TempFilePath &amp; TempFileName &amp; FileExtStr, FileFormat:=FileFormatNum
   On Error Resume Next
   With OutMail
      .To = Range("AQ6").Value
      .CC = Range("AQ7").Value
      .BCC = ""
      .Subject = Range("AQ8").Value
      .Body = Range("AQ9").Value
      .Attachments.Add Destwb.FullName
      .Display
      Application.Wait (Now + TimeValue("0:00:00"))
      Application.SendKeys "%s"
  
  End With
  On Error GoTo 0
  .Close savechanges:=False
  End With
  
  
  Kill TempFilePath &amp; TempFileName &amp; FileExtStr
  
  Set OutMail = Nothing
  Set OutApp = Nothing
  
  With Application
  .ScreenUpdating = True
  .EnableEvents = True
  End With
  
  Application.ScreenUpdating = True
  Set Sourcewb = Nothing
  Set Destwb = Nothing
  Set OutApp = Nothing
  Set OutMail = Nothing
  MsgBox ("Project Status Has been Sent")
   Else
  MsgBox "There must be atleast one contact in the To, or Cc, field"
  End If
  
  End Sub
  </code></pre>
  
  <p>Below is xml coding of color scheme which Microsoft excel save when you create any new color scheme / theme and it save the configuration file called xml file in the default path   <code>C:\Users\**UserName**\AppData\Roaming\Microsoft\Templates\Document Themes\Theme Colors</code></p>
  
  <p>so far i have reached to the conclusion that anyhow if we get able to incorporate that below xml code into the above vba code then we can get the desired result. But i dont know how.</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="true"?&gt;
  -&lt;a:clrScheme name="mycompanytheme"
  xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"&gt;
  -&lt;a:dk1&gt;
        &lt;a:sysClr lastClr="000000" val="windowText"/&gt;
  &lt;/a:dk1&gt;
  -&lt;a:lt1&gt;
        &lt;a:sysClr lastClr="FFFFFF" val="window"/&gt;
  &lt;/a:lt1&gt;
  -&lt;a:dk2&gt;
        &lt;a:srgbClr val="1F497D"/&gt;
  &lt;/a:dk2&gt;
  -&lt;a:lt2&gt;
        &lt;a:srgbClr val="EEECE1"/&gt;
  &lt;/a:lt2&gt;
  -&lt;a:accent1&gt;
        &lt;a:srgbClr val="D60037"/&gt;
  &lt;/a:accent1&gt;
  -&lt;a:accent2&gt;
        &lt;a:srgbClr val="B21DAC"/&gt;
  &lt;/a:accent2&gt;
  +&lt;a:accent3&gt;
        -&lt;a:accent4&gt;&lt;a:srgbClr val="FFCE00"/&gt;
  &lt;/a:accent4&gt;
  -&lt;a:accent5&gt;
        &lt;a:srgbClr val="009DD9"/&gt;
  &lt;/a:accent5&gt;
  -&lt;a:accent6&gt;
        &lt;a:srgbClr val="AF0637"/&gt;
  &lt;/a:accent6&gt;
        -&lt;a:hlink&gt;&lt;a:srgbClr val="80076B"/&gt;
  &lt;/a:hlink&gt;
        -&lt;a:folHlink&gt;&lt;a:srgbClr val="218535"/&gt;
  &lt;/a:folHlink&gt;
  &lt;/a:clrScheme&gt;
  </code></pre>