vba 复制并粘贴到另一个工作表的第一个空行，如果第一个单元格为空，则粘贴到前一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21340695/
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
Copy and paste to another sheet first empty row, pastes over the previous row if first cell is empty
提问by user3233081
I'm copying a range from one sheet (B14:I14) and pasting the values to another sheet in the first empty row. This works fine if the range has data in the first cell (B14).
我正在从一张纸 ( B14:I14)复制一个范围并将值粘贴到第一个空行中的另一张纸上。如果范围在第一个单元格 ( B14) 中有数据，则此方法可以正常工作。
When there is data in some of the cells but not B14, the next time it pastes over the same row as the last time I executed the macro.
当某些单元格中有数据而不是 B14 时，下次它会粘贴到与上次执行宏相同的行上。
The range will vary from having all cells containing data or just two. I need it to check that target row is empty, not just the first cell.
范围将不同于包含数据的所有单元格或仅包含两个单元格。我需要它来检查目标行是否为空，而不仅仅是第一个单元格。
Here is my macro:
这是我的宏：
Sub Save7()
Dim NextRow As Range
With Sheets("Sheet3")
Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
End With
Sheet1.Range("B14:I14").Copy
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing
End Sub
回答by Alex D
I'd suggest using this:
我建议使用这个：
Sub Save7()
Dim NextRow As Range
Set NextRow = Range("B" & Sheets("Sheet3").UsedRange.Rows.Count + 1)
Sheet1.Range("B14:I14").Copy
Sheet3.Activate
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing
End Sub
That should work well. UsedRange checks across all columns so if B is empty it won't overwrite it. Only problem is if the 1st row is blank, but you can just add header data there, or something and it won't be bothered by it ever again. When the first row is blank UsedRange.Rows.Count still outputs 1. Enjoy!
那应该很好用。UsedRange 检查所有列，因此如果 B 为空，则不会覆盖它。唯一的问题是如果第一行是空白的，但您可以在那里添加标题数据，或者其他东西，它不会再被它打扰。当第一行为空时 UsedRange.Rows.Count 仍然输出 1。享受吧！
