title: How to paste values and keep source formatting?
body: <p>I am trying to copy some values from a column in one workbook to another:</p>
  <p>Workbook 1</p>
  <pre><code>Column A
  10/02/1990
  41
  11/01/2017
  52
  </code></pre>
  <p>Workbook 2</p>
  <pre><code>Column I
  10/02/1990
  41
  11/01/2017
  52
  </code></pre>
  <p>If I simply copy my values from column 1 in workbook A and paste them to column I in workbook 2 I get results like so:</p>
  <pre><code>Column I
  34331
  41
  121092
  52
  </code></pre>
  <p>The formatting is lost/confused by Excel.</p>
  <p>So I created a button where users can paste this data using VBA like so:</p>
  <pre><code>Sub Paste3()
  Dim lastRow As Long
  On Error GoTo ErrorHandler
  
  lastRow = ActiveSheet.Range(&quot;H&quot; &amp; Rows.Count).End(xlUp).Row
  ActiveSheet.Range(&quot;H10&quot;).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False
  
  Exit Sub
  
  ErrorHandler:
  MsgBox &quot;Please Copy Values First.&quot;
  End Sub
  </code></pre>
  <p>The values keep their formatting. However, the cell format also changes.</p>
  <p>What I mean is, the cells on workbook 1 have a black border, and the font is also black and bold.</p>
  <p>I want to preserve workbook 2's font and cell border. This is:</p>
  <p>Grey border, RGB(191, 191, 191)<br />
  Grey Font (RGB 128, 128, 128)<br />
  Font Size: 11<br />
  Font: Calibri</p>
  <p>Essentially it needs to look like the column to the right.</p>
  <p><a href="https://i.stack.imgur.com/Qvslb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qvslb.png" alt="enter image description here" /></a></p>
  <p>I tried this, but it adds borders to ranges in my spreadsheet it isn't supposed to.</p>
  <pre><code>Sub Paste3()
  Dim lastRow As Long
  On Error GoTo ErrorHandler
  
  lastRow = ActiveSheet.Range(&quot;H&quot; &amp; Rows.Count).End(xlUp).Row
  ActiveSheet.Range(&quot;H10&quot;).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False
  
  Dim rng As Range
  Set rng = Range(&quot;H10:H&quot; &amp; lastRow)
  With rng.Borders
      .LineStyle = xlContinuous
      .Color = RGB(191, 191, 191)
      .Weight = xlThin
      .Font
  End With
      
  With rng.Font
      .TextColor = RGB(128, 128, 128)
      .Font.Name = &quot;Calibri&quot;
      .Size = 11
      .Bold = False
  End With
  Exit Sub
  
  ErrorHandler:
  MsgBox &quot;Please Copy Values First.&quot;
  End Sub
  </code></pre>
  <p>I would rather find an easier way of pasting these values and keeping their format without changing the cell format and font colour etc.</p>