sort excel table and copy visible cells invba

Posted on 2011-05-03
Last Modified: 2012-05-11
Hi experts
I'm trying to sort an excel table in vba , and then copy the visible cells only to a different table on a different sheet any ideas how I can do this in vba ?

Question by:Thrawn3000
    LVL 24

    Expert Comment

    Can you post a workbook?

    Author Comment

    Yes sure
    I want to filter the table in vba for all AAMI values under the brand field and then a specific date as an example and copy these visible cells to a different table. TestFile.xlsx
    LVL 24

    Accepted Solution

    Like this? Not sure what you meant by sorting, did you just mean filtering?
    Sub x()
    Dim rData As Range
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1").AutoFilter Field:=1, Criteria1:=DateSerial(2010, 7, 31)
        With .AutoFilter.Range
            On Error Resume Next
            Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rData Is Nothing Then
                rData.Copy Sheets("Sheet2").Range("A1")
            End If
        End With
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
    End Sub

    Open in new window


    Author Closing Comment

    Awesom, works perfectly

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now