Vba保留目标单元格格式

Question

我对vba比较陌生，所以请温柔一点:)

我已经审查了各种脚本，这些脚本据称在按ctrl c/ctrl v或复制&正在使用浆糊。不幸的是，我似乎无法让任何变化为我的意图工作。我想这可能是因为很多数据都是复制的。"粘贴"是从其他程序复制并粘贴到工作表中(因此复制并保持它来自的程序的格式)。我尝试使用的所有宏似乎都试图在单元格/工作表或工作簿之间复制时保留格式，并且在从另一个程序复制时不解决数据格式。

我正在寻找另一种方法。从逻辑的角度来看，我认为应该有一种方法对ctrl v或粘贴事件，有复制的数据存储为一个变量，剥离其格式，只粘贴原始值。我试过玩pastespecial，但我不知道如何强迫pastespecial(或用pastespecial代替粘贴)。

这里是一些代码示例，但它似乎不适合我。我一直收到:

不能运行宏"C:…Test.xlsm'!MyPaste "。该宏可能在此工作簿中不可用，或者所有宏都可能被禁用

宏被明确地启用并且代码被粘贴到[ThisWorkbook(code)]

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo Whoa
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue
    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo
    If UndoList = "Auto Fill" Then Selection.Copy
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    On Error GoTo 0
    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Answer 1

出现错误消息的原因是您的代码是事件处理程序

:

• MSDN Office在线参考:在Excel 2007中创建VBA宏来操作工作表
• MSDN Office联机参考:在Excel 2010中发生事件时运行VBA代码

和

• Chip Pearson MVP和全方位Excel chief: Events and Event Procedures In VBA
• Ozgrid MVP和全方位Excel VBA顶级香蕉:Excel VBA中的工作表事件

基本上是工作表。当用户更改工作表中的单元格时，将触发更改事件(Excel)。Excel传入Worksheet Object对象为sh，传入Range Object (Excel)为Target。你的代码然后使用这些对象(Ozgrid Excel VBA速成班第4课 - 通用对象)。

正如David Zemens所建议的，你需要使用Sheet对象的PasteSpecial方法。有关详细信息，请参见MSDN库:PasteSpecial Method [Excel 2003 VBA语言参考]。

，当你读完所有的阅读，你将准备复制粘贴我的代码如下:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo Whoa
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue
    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo
    If UndoList = "Auto Fill" Then Selection.Copy
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.PasteSpecial Paste:=xlPasteValues
    On Error GoTo 0
    '~~> Retain selection of the pasted data
    Target.Select
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

那么，bada bing bada bing，你有了你的工作代码，一些阅读应该帮助你更好地理解你的代码是做什么的，以及它是如何做到的。