vba 使用excel vba连续粘贴下一个空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17146830/
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 in the next empty cell in a row using excel vba
提问by Mike
I am writing a macro that copies a value from one excel worksheet, and pastes it into another. As seen below, I have a code that correctly copies and pastes my value into the correct worksheet, but I want it to paste into the next empty cell in row 3, instead of just cell "C3". All help is appreciated.
我正在编写一个宏，它从一个 Excel 工作表中复制一个值，然后将其粘贴到另一个工作表中。如下所示，我有一个代码可以正确地将我的值复制并粘贴到正确的工作表中，但我希望它粘贴到第 3 行的下一个空单元格中，而不仅仅是单元格“C3”。感谢所有帮助。
Private Sub CommandButton1_Click()
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Dim vMax As Variant
Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1
Set wbDATA = Workbooks.Open("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
With wbDATA.Sheets("Contract Task Summary(1)")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
If LastRow > 1 Then
.Range("C" & LastRow).Copy
wsMaster.Range("C" & 3).PasteSpecial xlPasteValues
wsMaster.Range("C" & 3).PasteSpecial xlPasteFormats
End If
End With
wbDATA.Close False
End Sub
采纳答案by varocarbas
This is the code you are looking for:
这是您正在寻找的代码：
Private Sub CommandButton1_Click()
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Dim vMax As Variant
Dim columnToPaste As Integer
Dim lastColumnToPaste As Integer
Dim lastColumn as Integer
Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1
Set wbDATA = Workbooks.Open("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
With wbDATA.Sheets("Contract Task Summary(1)")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
If LastRow > 1 Then
.Range("C" & LastRow).Copy
lastColumn = 3
lastColumnToPaste = lastColumn + 20
columnToPaste = lastColumn - 1
Do
columnToPaste = columnToPaste + 1
If IsEmpty(wsMaster.Cells(lastRow, columnToPaste)) Then
wsMaster.Cells(lastRow, columnToPaste).PasteSpecial xlPasteValues
wsMaster.Cells(lastRow, columnToPaste).PasteSpecial xlPasteFormats
Exit Do
End If
Loop While (columnToPaste < lastColumnToPaste)
End If
End With
wbDATA.Close False
End Sub
This is just a basic approach to how the problem should be solved. You should update some values dynamically (e.g., maximum row to check, given by the variable lastRowToPaste).
这只是解决问题的基本方法。您应该动态更新一些值（例如，要检查的最大行数，由变量给出lastRowToPaste）。
Note that writing/pasting between two different workbooks is very inefficient. In case of having to repeat this process for a long enough time, I would: open the input spreadsheet and store all the values in a temporary location (depending upon the size, in an array or in a temporary file), close it; open the destination spreadsheet and write the data from this location (without relying on copy/paste). This is a much faster approach to the problem.
请注意，在两个不同的工作簿之间写入/粘贴效率非常低。如果不得不重复这个过程足够长的时间，我会：打开输入电子表格并将所有值存储在一个临时位置（取决于大小，在一个数组或一个临时文件中），关闭它；打开目标电子表格并从此位置写入数据（不依赖于复制/粘贴）。这是解决问题的更快方法。
