使用 VBA 将过滤后的数据复制到另一个工作表 [英] Copy filtered data to another sheet using VBA

问题描述

我有两张纸.一个具有完整的数据，另一个基于应用于第一张纸的过滤器.

I have two sheets. One has the complete data and the other is based on the filter applied on the first sheet.

数据表名称:Data
过滤表的名称:Hoky

为了简单起见，我只提取了一小部分数据.我的目标是根据过滤器从数据表中复制数据.我有一个宏，它以某种方式工作，但它是硬编码的，是一个录制的宏.

I am just taking a small portion of data for simplicity. MY objective is to copy the data from Data Sheet, based on the filter. I have a macro which somehow works but its hard-coded and is a recorded macro.

我的问题是:

1. 每次的行数都不一样.(手动)
2. 列没有按顺序排列.

Sub TESTTHIS()
'
' TESTTHIS Macro
'
'FILTER
Range("F2").Select
Selection.AutoFilter
ActiveSheet.Range("$B$2:$F$12").AutoFilter Field:=5, Criteria1:="hockey"

'Data Selection and Copy
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Hockey").Select
Range("E3").Select
ActiveSheet.Paste

Sheets("Data").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hockey").Select
Range("D3").Select
ActiveSheet.Paste

Sheets("Data").Select
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hockey").Select
Range("C3").Select
ActiveSheet.Paste

End Sub

推荐答案

最好的方法

下面的代码是复制DBExtract表中的可见数据，粘贴到duplicateRecords表中，只有过滤后的值.我选择的范围是我的数据可以占用的最大范围.您可以根据需要更改它.

Below code is to copy the visible data in DBExtract sheet, and paste it into duplicateRecords sheet, with only filtered values. Range selected by me is the maximum range that can be occupied by my data. You can change it as per your need.

  Sub selectVisibleRange()

    Dim DbExtract, DuplicateRecords As Worksheet
    Set DbExtract = ThisWorkbook.Sheets("Export Worksheet")
    Set DuplicateRecords = ThisWorkbook.Sheets("DuplicateRecords")

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial


    End Sub

这篇关于使用 VBA 将过滤后的数据复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！