vba - 如果单元格 = 0，则将范围复制到主表并从主表中删除范围

Question

有 10 张表 (Sheet1...Sheet10)，表的范围相同 (C25:G34 & C42:N51)。



如果“总重量”列的值 > 0，则必须复制行。复制的行转到两个汇总表:

至 Westrock 表 -> Westrock 汇总表

至 DNP 表 -> DNP 汇总表

汇总表:
韦斯特洛克


汇总表:
DNP


我在 Mac 上，所以 PowerQuery 不是一个选项。我是 VBA 新手；这就是我到目前为止所拥有的:

Sub ToDNP()

Application.ScreenUpdating = False

Worksheets("Jupiter").Activate
Range("C42:N51").Select
Selection.Copy
Worksheets("To DNP").Activate
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Application.CutCopyMode = False

Worksheets("Windsor").Activate
Range("C42:N51").Select
Selection.Copy
Worksheets("To DNP").Activate
Range("C21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Worksheets("Orlando").Activate
Range("C42:N51").Select
Selection.Copy
Worksheets("To DNP").Activate
Range("C31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Worksheets("Woodland").Activate
Range("C42:N51").Select
Selection.Copy
Worksheets("To DNP").Activate
Range("C41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Dim rRow As Integer, rCol As Integer
Dim cRow As Integer, cCol As Integer

rCol = 3
rRow = 11
cCol = 14
cRow = 11

For cRow = 11 To 50

    If Cells(cCol, cRow).Value = "0" Then
        Range(Cells(rCol, rRow), Cells(cCol, cRow)).ClearContents
    End If
    rRow = rRow + 1

Next cRow

End Sub

这给了我一个错误:

Error: Cannot change part of a merged Cell

Answer 1

您收到错误是因为您交换了行和列值。行参数首先出现，然后是列参数。

您的代码应如下所示:

If Cells(cRow, cCol).Value = "0" Then
    Range(Cells(rRow, rCol), Cells(cRow, cCol)).ClearContents
End If

您正在尝试从“To DNP”工作表的第 3 行到第 14 行清除第 11 列，该工作表显然包含合并的单元格。