这将是数据,我希望能够采取所有的PP从sheet4和粘贴到表PDH_Handvoer在一个特定的范围,例如A11:A22。然后还取FA并将它们粘贴到同一张纸上,但范围为A30:A42,因此每个字母一个。
到目前为止,这就是代码,但它并没有完成我所需要的工作
Private Sub CommandButton1_Click()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet4")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("PDH_Handover")
Dim LRow1 As Long, LRow2 As Long, i As Long
LRow1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
For i = 2 To LRow1
If ws1.Cells(i, 1) = "pp" Then
ws1.Range(Cells(i, 2), Cells(i, 5)).Copy
ws2.Range("A" & LRow2 + 1).PasteSpecial xlPasteValues
End If
Next
End Sub
发布于 2018-08-23 04:05:07
您的代码正在工作,您只需要在粘贴新行后重新获取LRow2
值-否则您总是覆盖第一行(在您的情况下,最后复制的行为空,因此看起来没有发生任何事情,但实际上是空的)。
我还在最后添加了Application.CutCopyMode = False
,作为良好的实践(这将清除剪贴板)。
Private Sub CommandButton1_Click()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet4")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("PDH_Handover")
Dim LRow1 As Long, LRow2 As Long, i As Long
LRow1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
For i = 2 To LRow1
If ws1.Cells(i, 1) = "PP" Then
ws1.Range(Cells(i, 2), Cells(i, 5)).Copy
ws2.Range("A" & LRow2 + 1).PasteSpecial xlPasteValues
'Get new last row value
LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
End If
Next
Application.CutCopyMode = False
End Sub
相反,让我们完全摆脱Copy/Paste
,因为最好避免依赖于ActiveSheet
的语法
Private Sub CommandButton1_Click()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet4")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("PDH_Handover")
Dim LRow1 As Long, LRow2 As Long, i As Long
LRow1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
For i = 2 To LRow1
If ws1.Cells(i, 1) = "PP" Then
ws2.Range(ws2.Cells(LRow2 + 1, 1), ws2.Cells(LRow2 + 1, 4)).Value = _
ws1.Range(ws1.Cells(i, 2), ws1.Cells(i, 5)).Value
'Get new last row value
LRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
End If
Next
End Sub
https://stackoverflow.com/questions/51974035
复制相似问题