title: PasteSpecial does not keep source formatting when copying range in Excel
body: <p>I am copying a range of cells from an existing workbook to a temporary workbook and want to keep the exact formatting of the source workbook. 
  I'm using a modified version of the RangetoHTML function by Ron de Bruin.</p>
  
  <p>The purpose is to copy a range from the source workbook to the body of a mail.</p>
  
  <p>But, when I run the macro, it pastes the range in the mail <s>only in values</s> in a different format (see link below). All other paste type of the PasteSpecial either fails or give the same result.</p>
  
  <p>I've tried every paste type of the PasteSpecial method with no luck.</p>
  
  <p>If I try to specify more arguments for the PasteSpecial function it throws a 1004 error (I don't need to use anything else than the default values though).</p>
  
  <p>The source workbook has merged cells but I tried removing the merged cells but that didn't change the output.</p>
  
  <p>This code is called multiple times as I want to insert ranges from different workbook. The result is always the correct range but with no formatting.</p>
  
  <p>It is run from Access as I'm trying to run this script from a form. It is in a public module and called when creating the mail body in another module.
  Here is an extract of the part that trows an error when removing the comments:</p>
  
  <pre><code>Public Function RangetoHTML(rng As Range)
  ' Changed by Ron de Bruin 28-Oct-2006
  
      Dim xlApp As Excel.Application
      Dim TempWB As Excel.workbook
  
      Set TempWB = xlApp.Workbooks.Add(1)
      rng.Copy 'This is passed as an argument of the RangetoHTML function
      TempWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues ', Operation:=-4142, SkipBlanks:=True, Transpose:=False
      TempWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats ', Operation:=-4142, SkipBlanks:=True, Transpose:=False
      TempWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths ', Operation:=-4142, SkipBlanks:=True, Transpose:=False
  
  'The rest is similar to the original version of RangetoHTML
  
      'Close TempWB
      TempWB.Close savechanges:=False
      xlApp.Quit
  
      Set TempWB = Nothing
      Set xlApp = Nothing
  
  End Function
  </code></pre>
  
  <p>I want the range to be displayed in the body of the mail the same as in the source workbook .</p>
  
  <p>I can't seem to find any problem with this code that will prevent the formatting to be copied over despite all my attempts.</p>
  
  <p><strong>EDIT</strong> :</p>
  
  <p>If I change the line  :</p>
  
  <pre><code>Set TempWB = xlApp.Workbooks.Add(1)
  </code></pre>
  
  <p>to :</p>
  
  <pre><code>Set TempWB = Workbooks.Add(1)
  </code></pre>
  
  <p>then the output is in the correct formatting, but there is an instance of Excel that stays open in the task manager after the sub ends.</p>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>Here is some screenshots to help explain the issue (with sensitive information blurred).</p>
  
  <p>This is the output I'm expecting:
  <img src="https://i.stack.imgur.com/BCmKi.png" alt="Correct output"></p>
  
  <p>And here is the actual result I get when using the code provided above:
  <img src="https://i.stack.imgur.com/l4kZal.png" alt="What I got"></p>
  
  <p>I've checked the content of the temp file where the table is pasted and the formatting is identical to what is pasted in the mail body. That tells me that the <code>PasteSpecial</code> is the culprit here and not the rest of the RangetoHTML function.</p>
  
  <p><strong>EDIT 3:</strong></p>
  
  <p>I also tried to use the plain <code>Paste</code> function and I got the same result as pictured above:</p>
  
  <pre><code>rng.Copy
  TempWB.Worksheets(1).Paste
  </code></pre>
  
  <p><strong><code>PasteSpecial</code> actually does nothing no matter which arguments I provide.</strong></p>