How to paste values and keep source formatting?

Question

I am trying to copy some values from a column in one workbook to another:

Workbook 1

Column A
10/02/1990
41
11/01/2017
52

Workbook 2

Column I
10/02/1990
41
11/01/2017
52

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:

Column I
34331
41
121092
52

The formatting is lost/confused by Excel.

So I created a button where users can paste this data using VBA like so:

Sub Paste3()
Dim lastRow As Long
On Error GoTo ErrorHandler

lastRow = ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("H10").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

Exit Sub

ErrorHandler:
MsgBox "Please Copy Values First."
End Sub

The values keep their formatting. However, the cell format also changes.

What I mean is, the cells on workbook 1 have a black border, and the font is also black and bold.

I want to preserve workbook 2's font and cell border. This is:

Grey border, RGB(191, 191, 191)
Grey Font (RGB 128, 128, 128)
Font Size: 11
Font: Calibri

Essentially it needs to look like the column to the right.

I tried this, but it adds borders to ranges in my spreadsheet it isn't supposed to.

Sub Paste3()
Dim lastRow As Long
On Error GoTo ErrorHandler

lastRow = ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("H10").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

Dim rng As Range
Set rng = Range("H10:H" & 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 = "Calibri"
    .Size = 11
    .Bold = False
End With
Exit Sub

ErrorHandler:
MsgBox "Please Copy Values First."
End Sub

I would rather find an easier way of pasting these values and keeping their format without changing the cell format and font colour etc.

Paste Values pastes, well, the values. If you want to keep the formatting you need to paste more than just the values. — Mathieu Guindon
Try recording a macro while you manually perform the paste special operation, and see what parameters Excel is given for the PasteSpecial call. — Mathieu Guindon
Paste Values and Formats. Check this post: stackoverflow.com/questions/25461314/… — tlemaster

Tim Williams Tim Williams · Accepted Answer · 2017-01-18T18:31:24

There's a PasteSpecial option for this:

ActiveSheet.Range("H10").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                 Operation:= xlNone, SkipBlanks:=False, Transpose:=False

How to paste values and keep source formatting?

2 Answers