如果单元格= 0，则将范围复制到主表并从主表中删除范围 [英] Copy ranges to master table and remove ranges from master table if cell = 0

问题描述

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

There are 10 Sheets (Sheet1...Sheet10) with tables in the same range (C25:G34 & C42:N51).

如果总重量"列的值大于0，则必须复制行.复制的行将进入两个汇总表:

The rows have to be copied if the 'Total Weight' column has value > 0. The copied rows go to two summary tables:

至Westrock表-> Westrock摘要表

To Westrock Table -> Westrock Summary Table

到DNP表-> DNP摘要表

To DNP Table -> DNP Summary Table

摘要表: 韦斯特洛克

Summary Table: Westrock

摘要表: DNP

我在Mac上，因此无法使用PowerQuery.我是VBA的新手.这是我到目前为止的内容:

I'm on Mac, so PowerQuery is not an option. I'm new to VBA; this is what I have so far:

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

推荐答案

由于出现了行和列值交换的问题，因此出现错误.首先是row参数，然后是 列参数.

You're getting the error because you have the row and column values swapped around. The row parameter comes first and then comes the column parameter.

您的代码应如下所示:

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

您正在尝试清除到DNP"工作表的第3至14行中的第11列，该表显然包含合并的单元格.

You are trying to clear column 11 from rows 3 to 14 of the "To DNP" worksheet, which obviously contains merged cells.

这篇关于如果单元格= 0，则将范围复制到主表并从主表中删除范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！