Excel VBA复制筛选数据中的区域并追加到另一工作表的表尾

sshcrbum  于 2022-12-05  发布在  其他
关注(0)|答案(1)|浏览(358)

我遇到了一个问题,但我的VBA是新手,无法找出我的代码出了什么问题。
我想实现的是:

步骤1.在工作表1中,单元格B8:BR8的标题下有大量数据
步骤2. I在单元格BE8上筛选非空白
第3步.我复制BE8:BN8下的筛选数据(不包括标题,我不需要所有数据,因此我只复制完整数据的子集)
第4步.我转到工作表2,在那里我有一个填充的表,其C8:L8中的标题与工作表1中的标题BE8:BN8完全对应
步骤5.我要将这组新复制的数据附加到工作表2中此表格的结尾
第6步。我想返回工作表1并删除一些筛选的数据,特别是标题BE8、BK8:BN8下的数据

下面是我的尝试,我试图从另一个代码:

Sub TransferData()

    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim RngBeforeFilter As Range, RngAfterFilter As Range
    Dim LCol As Long, LRow As Long

    With ThisWorkbook
        Set WS1 = .Sheets("Sheet1")
        Set WS2 = .Sheets("Sheet2")
    End With

    With WS1
        'Make sure no other filters are active.
        .AutoFilterMode = False

        'Get the correct boundaries.
        LRow = .Range("BE" & .Rows.Count).End(xlUp).Row
        LCol = .Range("BE8:BN8").Column

        'Set the range to filter.
        Set RngBeforeFilter = .Range(.Cells(1, 2), .Cells(LRow, LCol)).Offset(1)
        RngBeforeFilter.Rows(8).AutoFilter Field:=56, Criteria1:="<>"

        'Set the new range, but use visible cells only.
        Set RngAfterFilter = .Range(.Cells(1, 7), .Cells(LRow, LCol)).SpecialCells(xlCellTypeVisible)

        'Copy the visible cells from the new range.
        RngAfterFilter.Copy WS2.Range("C65536").End(xlUp)

        'Clear filtered data (not working)
        Sheets("Sheet1").Range("B8", Range("B8").End(xlDown)).SpecialCells(xlCellTypeVisible).ClearContents
        .ShowAllData

    End With

End Sub

如果你能提供任何帮助,我将不胜感激。
谢谢Jacque

4c8rllxm

4c8rllxm1#

这里有几个问题:

.Range("BE8:BN8").Column

可能没有按照您的预期执行-它只会返回BE的列号(即57)。
RngBeforeFilter不执行任何操作-您可以只使用

.Rows(8).AutoFilter Field:=56, Criteria1:="<>"

您说要复制BE:BN中的数据,但您从列A(即单元格(1,7))启动RngAfterFilter。

WS2.Range("C65536").End(xlUp)

提供使用的最后一行,而您将希望粘贴到下一行。
您正在清除B列,而不是BE、BK和BN列。
因此,请尝试以下操作:

Sub TransferData()

Dim WS1 As Worksheet, WS2 As Worksheet
Dim RngBeforeFilter As Range, RngAfterFilter As Range
Dim BECol As Long, BNCol As Long, LRow As Long

With ThisWorkbook
    Set WS1 = .Sheets("Sheet1")
    Set WS2 = .Sheets("Sheet2")
End With

With WS1
    'Make sure no other filters are active.
    .AutoFilterMode = False

    'Get the correct boundaries.
    LRow = .Range("BE" & .Rows.Count).End(xlUp).Row
    BECol = .Range("BE8").Column
    BNCol = .Range("BN8").Column

    'Set the range to filter.
    .Rows(8).AutoFilter Field:=BECol - 1, Criteria1:="<>"

    'Set the new range, but use visible cells only.
    Set RngAfterFilter = .Range(.Cells(9, BECol), .Cells(LRow, BNCol)).SpecialCells(xlCellTypeVisible)
    'Copy the visible cells from the new range.
    RngAfterFilter.Copy WS2.Range("C65536").End(xlUp).Offset(1)

    'Clear filtered data
    .Range("BE9", Range("BE8").End(xlDown)).SpecialCells(xlCellTypeVisible).ClearContents
    .Range("BK9", Range("BK8").End(xlDown)).SpecialCells(xlCellTypeVisible).ClearContents
    .Range("BN9", Range("BN8").End(xlDown)).SpecialCells(xlCellTypeVisible).ClearContents
    .ShowAllData

End With

End Sub

相关问题