vba - 将行复制/粘贴到匹配的命名工作表

Question

我有一个工作表“列表”，其中包含我需要复制到其他工作表的数据行。在“列表”的“J”列中，有一个名称(Matthew、Mark、Linda 等)指定该行的数据是谁。

其中每个名称(总共 22 个)都有一个具有相同名称的匹配电子表格。我希望将“J”列中显示“Linda”的所有行粘贴到工作表“Linda”，将所有带有“Matthew”的行粘贴到工作表“Matthew”等。

我下面有一些代码，大部分都可以工作，但我必须为所有 22 个名称/工作表重写它。

有没有办法循环遍历所有工作表，粘贴具有匹配名称的行？另外，下面的代码运行速度非常慢，而且我使用的数据集有 200 到 60,000 行需要排序和粘贴，这意味着如果它在像我当前正在处理的小数据集上运行缓慢，而且仅对于一张纸来说，对于大数据集来说速度会非常慢。

Sub CopyMatch()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = Worksheets("List")
    Set Target = Worksheets("Linda")

    j = 4     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("J4:J1000")   ' Do 1000 rows
        If c = "Linda" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

Answer 1

除非您在此处看不到的地方关闭了计算，否则每次您复制一行时，Excel 都会重新计算 - 即使您的工作表不包含公式。

如果您还没有这样做，只需输入:

application.calculation=xlcalculationmanual

在开始循环之前:

application.calculation=xlcalculationautomatic

退出循环后将大大加快循环速度。为了获得额外的效果，您可以在关闭计算设置之前使用变量来存储计算设置，并在最后恢复该设置，例如

dim lCalc as long
lCalc = application.calculation
application.calculation = xlcalculationmanual
for ... next goes here
application.calculation = lCalc

还要考虑其他设置，例如:application.screenupdating=False|True。

按您选择的名称对数据进行排序，然后按您想要的任何其他排序。这样您就可以通过 22 个步骤跳过任何尺寸表(因为您说您有 22 个名字)。

复制数据的方式取决于偏好以及数据量。一次复制一行可以节省内存并且几乎可以保证工作，但速度较慢。或者，您可以识别每个人数据的顶部和底部行，并将整个 block 复制为单个范围，但存在超出大工作表中大块上可用内存的风险。

假设您要检查的范围内的名称列中的值始终是 22 个名称之一，那么如果您首先按该列排序，则可以使用该列中的值来确定目的地，例如:

dim sTarget as string
dim rng as range
sTarget = ""
For Each c In Source.Range("J4:J1000") ' Do 1000 rows
    if c <> "" then ' skip empty rows
        if c <> sTarget then ' new name block
            sTarget = c
            Set Target = Worksheets(c)
            set rng = Target.cells(Target.rows.count, 10).end(xlup) ' 10="J"
            j = rng.row + 1 ' first row below last name pasted
        end if
        Source.Rows(c.Row).Copy Target.Rows(j)
        j = j + 1
    end if
Next

这很节省内存，因为您要逐行进行操作，但速度仍然相当快，因为​​您只在名称更改时重新计算 Target 并重置 j。