vba 将剪贴板中的值粘贴到打开的 Excel 电子表格的第一空行中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41001665/
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
Pasting Values from Clipboard into the first empty row of open Excel spreadsheet
提问by Koda
I have a dataframe from R that I need to paste into the first empty row of an open Excel spreadsheet.
我有一个来自 R 的数据框，我需要将其粘贴到打开的 Excel 电子表格的第一个空行中。
I have tried numerous things.
我尝试了很多东西。
This code throws a "Run-time error '1004': Application-defined or object-defined error".
此代码引发“运行时错误‘1004’：应用程序定义或对象定义错误”。
Dim NextRow As Range
Set NextRow = Range("B" & Sheets("TC-9").UsedRange.Rows.Count + 1)
Worksheets("TC-9").Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
I also tried using xlUp to look from the bottom up to find the first empty row.
我还尝试使用 xlUp 从下往上查找第一个空行。
Cells(Range("C1000000000").End(xlUp).Row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
回答by Ash Notts
Try this
尝试这个
dim lastrow as integer
With Worksheets("TC-9")
lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
.Range("A" & lastrow + 1).PasteSpecial xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
回答by Amit Panasara
Appending into last of document
附加到文档的最后
Set Range2 = ActiveDocument.Content
Range2.Collapse Direction:=wdCollapseEnd
Range2.Paste
回答by YowE3K
In your PasteSpecialline, you are specifying a Rangeas Range("A" & NextRow). NextRowhas been defined as a Rangeitself, and concatenating a Stringwith a Rangeisn't valid.
在您的PasteSpecial行中，您指定 a Rangeas Range("A" & NextRow)。 NextRow已被定义为 aRange本身，并且将 aString与 a连接Range是无效的。
Change your code as follows:
更改您的代码如下：
Dim NextRow As Long
NextRow = Sheets("TC-9").Range("B" & Sheets("TC-9").Rows.Count).End(xlUp).Row + 1
Worksheets("TC-9").Range("A" & NextRow).PasteSpecial
Note: This will copy a single value from the clipboard. If you want multiple values copied to a single row, it will work if they are tab-delimited but not if they are comma-delimited. (It can probably be done, but would require a bit more work.)
注意：这将从剪贴板复制单个值。如果您想将多个值复制到一行，如果它们是制表符分隔的，那么它会起作用，但如果它们是逗号分隔的，则不起作用。（它可能可以完成，但需要更多的工作。）
In your second piece of code you used Range("C1000000000")but Excel currently has a limitation of 1,048,576 rows.
在您使用的第二段代码中，Range("C1000000000")但 Excel 当前有 1,048,576 行的限制。
回答by Werrf
If I'm understanding, you're trying to paste a value in that's in the Windows clipboard. The problem is that you can't use Paste Values when pasting from Windows, because it's not copying an Excel object.
如果我理解，您正在尝试在 Windows 剪贴板中粘贴一个值。问题是从 Windows 粘贴时不能使用粘贴值，因为它不是复制 Excel 对象。
Rather than using Range.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, try just using PasteSpecial on its own:
与其使用Range.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone，不如尝试单独使用 PasteSpecial：
Range.PasteSpecial
You could also just use the .Paste method, but you'll need to select your range first:
您也可以只使用 .Paste 方法，但您需要先选择您的范围：
Range("A1").select
ActiveSheet.Paste
It doesn't matter what method you use to find the Range, just how you paste it. I prefer the former method, but both should work.
使用什么方法查找 并不重要，重要的是您Range如何粘贴它。我更喜欢前一种方法，但两者都应该有效。
