Copy filtered data to another sheet using VBA

Question

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

Name of the data sheet : Data
Name of the filtered 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.

My problems are:

1. The number of rows is different everytime. (manual effort)
2. Columns are not in order.

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

Solution

Best way of doing it

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

Comments

[Ananya Pandey]

Can you write an example (complete lne of code)so that i can apply it on my sheet.

[Ananya Pandey]

It worked fine. Thanks. and i got the idea although i have to know more about the offset function.

[Arpan Saini]

This is a very time consuming process and take lot of time to read each row and copy it to another sheet, Sheet will hang when you have data in thousands record

[ashleedawg]

@AnanyaPandey This isn't a "free code writing service". Please see How to Ask and help center.

[Ananya Pandey]

With all due respect Mister, I am not asking for free service, I tried i failed and seeking help here i am learning, Thank you for your valuable comment.

[Florian Gee]

SpecialCells(xlCellTypeVisible) was exactly what I was looking for. Thanks!