使用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.

数据表名称：数据

过滤后的数据表名称： 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工作表中的可见数据，然后将其粘贴到重复记录工作表中，仅使用过滤后的值。我选择的范围是我的数据可以占用的最大范围。您可以根据需要进行更改。

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屋！