粘贴到活动单元格 VBA excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27385819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
paste to active cell VBA excel
提问by Vince Earl
I need to paste text from a Word table to an Excel worksheet, but because some of the cells in Word have line breaks that I need to preserve in Excel, a straight copy/paste is not an option--Excel interprets any line breaks as cell breaks. To work around this, I'm replacing the line breaks in Word with dummy text (e.g., "@@@"), then pasting into Excel, and finally running find/replace to replace the dummy text with an Excel-compatible line break (alt+0010).
我需要将 Word 表格中的文本粘贴到 Excel 工作表中，但由于 Word 中的某些单元格具有我需要在 Excel 中保留的换行符，因此不能直接复制/粘贴 - Excel 将任何换行符解释为细胞断裂。为了解决这个问题，我将 Word 中的换行符替换为虚拟文本（例如，“@@@”），然后粘贴到 Excel 中，最后运行查找/替换以使用 Excel 兼容的换行符替换虚拟文本(alt+0010)。
I recorded a macro to do this:
我录制了一个宏来做到这一点：
Sub IDA()
'
' IDA Macro
'
' Keyboard Shortcut: Ctrl+q
'
Sheets("Deployment").Select
ActiveWindow.SmallScroll Down:=-12
Range("C2").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
Selection.Replace What:="@@@", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
But when I move the the next block of text to paste, it pastes back to the original destination. I can see the sheet ("Deployment") and range ("C2") are both hard-coded. I tried removing those lines, but that broke the script--nothing happens when I run it.
但是当我移动下一个要粘贴的文本块时，它会粘贴回原始目的地。我可以看到工作表（“部署”）和范围（“C2”）都是硬编码的。我尝试删除这些行，但这破坏了脚本——当我运行它时没有任何反应。
I could simplify the script to just do the search and replace, but I don't trust the other users to use the paste special option when they paste into Excel, which means the spreadsheet may end up with other formatting from Word that I don't want.
我可以简化脚本以只进行搜索和替换，但我不相信其他用户在粘贴到 Excel 时会使用粘贴特殊选项，这意味着电子表格可能会以我不知道的其他 Word 格式结束'不想。
How do I modify my script so it simply pastes into the active worksheet and cell?
如何修改我的脚本，使其简单地粘贴到活动工作表和单元格中？
回答by ya_dimon
try to remove the 2 hardcoded lines, and use:
尝试删除 2 个硬编码行，并使用：
Selection.PasteSpecial ....
so the end code:
所以最终代码：
Sub IDA()
'
' IDA Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveWindow.SmallScroll Down:=-12
Selection.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
Selection.Replace What:="@@@", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
回答by Vince Earl
This turned out to be an easy fix. Simply clicking the "Use Relative References" button (directly under the record button) prior to recording the macro did the trick. It yielded the following script:
结果证明这很容易解决。在录制宏之前，只需单击“使用相对引用”按钮（直接在录制按钮下方）即可。它产生了以下脚本：
Sub IDA()
'
' IDA Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Selection.Replace What:="@@@", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Not sure if this is the most efficient code, but it will do the job for myself and the three other people doing this a few times per quarter. There's never enough data being copied to notice a difference. A lot of effort to satisfy the bellyaching of one coworker who couldn't be bothered with a whopping two keystrokes and ensure the paste special option is used.
不确定这是否是最有效的代码，但它可以为我自己和其他三个人每季度做几次。没有足够的数据被复制来注意到差异。付出了很多努力来满足一位同事的腹痛，他不会被两次击键所困扰，并确保使用粘贴特殊选项。
