Selecting first 10 rows of filtered data

  • Hello all,


    I'm trying to select the first 10 rows of filtered data and then paste these rows as values on to a separate sheet.


    This is roughly what the data looks like.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [td]

    G

    [/td]


    [td]

    H

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    Enquiry Volumes By Classification

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td][/td]


    [td]

    Start Date: Saturday, 1 July 2017

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td][/td]


    [td]

    End Date: Monday, 31 July 2017

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    ID

    [/td]


    [td]

    BSC Level 3

    [/td]


    [td]

    BSC Level 4

    [/td]


    [td]

    BSC Level 5

    [/td]


    [td]


    [/td]


    [td]

    Service Type

    [/td]


    [td]


    [/td]


    [td]

    Enquiry Volume

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    RFS_Bin Repair_

    [/td]


    [td]


    [/td]


    [td]

    Waste

    [/td]


    [td]

    Bin Repair

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    RFS

    [/td]


    [td]

    4351

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    RFA_Charges_Search

    [/td]


    [td]


    [/td]


    [td]

    Property

    [/td]


    [td]

    Charges

    [/td]


    [td][/td]


    [td]

    Search

    [/td]


    [td]

    RFA

    [/td]


    [td]

    3775

    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]

    RFS_Domestic_Complaint

    [/td]


    [td]


    [/td]


    [td]

    Animal

    [/td]


    [td]

    Domestic

    [/td]


    [td]


    [/td]


    [td]

    Complaint

    [/td]


    [td]

    RFS

    [/td]


    [td]

    2946

    [/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    RFS_Mobile_

    [/td]


    [td]


    [/td]


    [td]

    Waste

    [/td]


    [td]

    Mobile

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    RFS

    [/td]


    [td]

    2477

    [/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    RFS_Business_New

    [/td]


    [td]


    [/td]


    [td]

    Waste

    [/td]


    [td]

    Business

    [/td]


    [td]


    [/td]


    [td]

    New

    [/td]


    [td]

    RFS

    [/td]


    [td]

    2462

    [/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    RFS_Plan_

    [/td]


    [td]


    [/td]


    [td]

    City

    [/td]


    [td]

    Plan

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    RFS

    [/td]


    [td]

    2143

    [/td]


    [/tr]


    [tr]


    [td]

    17

    [/td]


    [td]

    RFS_Parking_

    [/td]


    [td]


    [/td]


    [td]

    Permit

    [/td]


    [td]

    Parking

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    RFS

    [/td]


    [td]

    1805

    [/td]


    [/tr]


    [tr]


    [td]

    25

    [/td]


    [td]

    RFI_Non Issue_

    [/td]


    [td]


    [/td]


    [td]

    City

    [/td]


    [td]

    Non Issue

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    RFI

    [/td]


    [td]

    1673

    [/td]


    [/tr]


    [tr]


    [td]

    26

    [/td]


    [td]

    RFI_Reunion_

    [/td]


    [td]


    [/td]


    [td]

    Animal

    [/td]


    [td]

    Reunion

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    RFI

    [/td]


    [td]

    1591

    [/td]


    [/tr]


    [tr]


    [td]

    27

    [/td]


    [td]

    RFA_Registration_Domestic

    [/td]


    [td]


    [/td]


    [td]

    Animal

    [/td]


    [td]

    Registration

    [/td]


    [td]


    [/td]


    [td]

    Domestic

    [/td]


    [td]

    RFA

    [/td]


    [td]

    1559

    [/td]


    [/tr]


    [tr]


    [td]

    28

    [/td]


    [td]

    RFS_Missed_Business

    [/td]


    [td]


    [/td]


    [td]

    Waste

    [/td]


    [td]

    Missed

    [/td]


    [td]


    [/td]


    [td]

    Business

    [/td]


    [td]

    RFS

    [/td]


    [td]

    1486

    [/td]


    [/tr]


    [tr]


    [td]

    30

    [/td]


    [td]

    RFS_Type_Development

    [/td]


    [td][/td]


    [td]

    City

    [/td]


    [td]

    Type

    [/td]


    [td][/td]


    [td]

    Development

    [/td]


    [td]

    RFS

    [/td]


    [td]

    1337

    [/td]


    [/tr]


    [/TABLE]


    This table is on a sheet called 'Data Dump'. The headings for the data are on Row 8, from 8 down the data is filtered and sorted by highest volume of enquiries.
    There is data in column B & E, but it's been filtered out. There are formulas under the ID heading.
    The data is laid out this way as it's pasted from another report.


    What I want to do is have a macro that copies the data from 'BSC Level 3' to the data in column H, the top 10 enquiries by volume and pastes it as values on a sheet called 'Top 10 Enquiries' in C3. So in this case, B10 to H27, but depending on the report, the row numbers will change from week to week. Could someone please help me with this? I haven't used VBA in many years and what little i did know has escaped me. I feel this is quite easy and am sure I've been able to do this in the past!

  • Re: Selecting first 10 rows of filtered data


    Try this


    [vb]Option Explicit


    Sub kTest()

    Dim Data As Range
    Dim CopyRng As Range
    Dim r As Long

    With Worksheets("Data Dump")
    r = .Range("a" & .Rows.Count).End(3).Row
    Set Data = .Range("a8:h" & r)
    End With

    With Data
    .AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items
    Set CopyRng = .Offset(1, 1).Resize(.Rows.Count, .Columns.Count - 1).SpecialCells(12)
    If Not CopyRng Is Nothing Then
    CopyRng.Copy Worksheets("Top 10 Enquiries").Range("c3")
    End If
    End With

    End Sub[/vb]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!