如何粘贴值并保持源格式? [英] How to paste values and keep source formatting?

问题描述

我正在尝试将一些值从一个工作簿中的列复制到另一个:

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

工作簿1

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

工作簿2

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

如果仅从工作簿A的第1列复制值并将其粘贴到工作簿2的第I列，则会得到如下结果:

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

Excel丢失/混淆了格式.

The formatting is lost/confused by Excel.

因此，我创建了一个按钮，用户可以在其中使用VBA粘贴此数据，如下所示:

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.

我的意思是，工作簿1上的单元格具有黑色边框，并且字体也为黑色和粗体.

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

我想保留工作簿2的字体和单元格边框.这是:

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

灰色边框，RGB(191，191，191)
灰色字体(RGB 128、128、128)
字体大小:11
字体:Calibri

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.

推荐答案

对此有一个PasteSpecial选项:

There's a PasteSpecial option for this:

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

这篇关于如何粘贴值并保持源格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！