基于单元格输入复制和粘贴数据

Question

我正在尝试根据每个行(列U)的特定列中的值，将行从原始表(门票销售)复制并粘贴到新表(瑞秋)。我希望瑞秋的所有销售记录都被复制到另一张纸上。第二个工作表的格式没有更改。这就是我到目前为止所知道的：

Sub CopyRachelDataPaste()    
    Dim i, LastRow

    'Get Last Row
    LasRow = Sheets("Ticket Sales").Range("A" & Rows.Count).End(xlUp).Row
    'Clear Contents
    Sheets("Rachel").Range("A2:AB3000").ClearContents

    For i = 2 To LastRow
        If Sheets("Ticket Sales").Cells(i, "U").Value = "Rachel" Then 
            Sheets("Ticket Sales").Cells(i, "U").EntireRow.Copy Destination:=Sheets("Rachel").Range("A" & Rows.Count).End(xlUp).Offset(1)    
        End If
    Next i
End Sub

Answer 1

您必须在所有范围定义中正确引用工作表

如下所示：

Option Explicit

Sub CopyRachelDataPaste()
    Dim i As Long, lastRow As Long

    Worksheets("Rachel").Range("A2:AB3000").ClearContents 'Clear Contents
    With Worksheets("Ticket Sales") '<--| reference ticket worksheet
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row '<--| get referenced worksheet column "A" last non empty row    
        For i = 2 To lastRow
            If .Cells(i, "U").Value = "Rachel" Then .Cells(i, "U").EntireRow.Copy Destination:=Worksheets("Rachel").Range("A" & Worksheets("Rachel").Rows.Count).End(xlUp).Offset(1)
        Next i
    End With
End Sub