excel - 如何将特定列和行复制到另一个工作表

Question

我有一个包含 A:M 列和第 1 到 5000 行的工作表。当将大于 0 的数字添加到 L 和 M 列时，我想将行复制到另一个工作表。我也只需要 A:F 和 K:M 列在新工作表上

标签： excelvba

Answer 1

您需要命名源工作表和目标工作表，但下面的代码应该可以解决问题。

Sub SheetTransfer()

Dim i As Long
Dim j As Long
Dim t As Double
Dim LastRow As Long
Dim ws1 As String
Dim ws2 As String

'name source worksheet here
ws1 = "Sheet1"
'name target worksheet here
ws2 = "Sheet2"

'set the threshold value for a row to be copied over
t = 0

' set to column L
j = 12

    For i = 1 To 5000

        If Worksheets(ws1).Cells(i, j).Value > 0 Or Cells(i, j + 1).Value > t Then

            'find last row of target worksheet
            With Worksheets(ws2)
                LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            End With

            'copy/paste columns A-F
            Worksheets(ws1).Range(Cells(i, 1), Cells(i, 6)).Copy
            Worksheets(ws2).Cells(LastRow + 1, 1).PasteSpecial xlPasteValues
            'copy paste columns K-M
            Worksheets(ws1).Range(Cells(i, 11), Cells(i, 13)).Copy
            Worksheets(ws2).Cells(LastRow + 1, 11).PasteSpecial xlPasteValues

        End If
    Next i

End Sub