filtered view on separate worksheets

Posted on 2012-09-05
Last Modified: 2012-09-06
I have flat data listing on worksheet 1 of an excel file and wish to create views on subsequent worksheets based on filtered settings\sorts of worksheet 1 data.

I have attached a sample XLS of the data and what I would like to show in each worksheet.

Hoping someone can get me off to a good start.

Question by:Needy11
    LVL 18

    Accepted Solution



    Sub kTest()
        Dim k, i As Long, r As Range
        Dim ShtData As Worksheet
        Dim ShtNew  As Worksheet
        Const FilterCol As Long = 6
        Set ShtData = Worksheets("ABA")
        Set r = ShtData.Range("a1").CurrentRegion
        k = r.Columns(FilterCol)
        With CreateObject("scripting.dictionary")
            .comparemode = 1
            For i = 2 To UBound(k, 1)
                If Len(k(i, 1)) Then .Item(k(i, 1)) = Empty
            If .Count Then
                k = .keys
                Application.ScreenUpdating = False
                On Error Resume Next
                For i = 0 To UBound(k)
                    Set ShtNew = Worksheets(k(i))
                    If Err.Number <> 0 Then
                        Set ShtNew = Worksheets.Add
                        ShtNew.Name = k(i): Err.Clear
                    End If
                    ShtData.Cells(2, r.Columns.Count + 2) = "=rc[-" & r.Columns.Count - FilterCol + 2 & "]=""" & k(i) & """"
                    r.AdvancedFilter 2, ShtData.Cells(1, r.Columns.Count + 2).Resize(2), ShtNew.Cells(1)
                    Set ShtNew = Nothing
            End If
        End With
        ShtData.Cells(1, r.Columns.Count + 2).Resize(2).ClearContents
    End Sub

    Open in new window


    Author Comment

    This hasn't quite worked for me. I have attached the result I get. It only shows the first cell of each record on the worksheets.

    Can you tell me is the data filtered i..e queried or copied in this? I would prefer for it to be queried so if the main worksheet is updated all otheres will just need a refresh.

    Many thanks


    PS I have reattached the result file. It creates a new worksheet for Applied Behaviour Analysis but not for any of the other course names. Is there a loop I need to insert?



    Author Comment

    Ok I figured it I had to make sure the worksheets that were being populated did not exist initially.

    can you tell me where the code need to change if I need to expand the cell region. The sheet I gave was just a small one 5\6 columns but actually there are close to 20. I also need to make sure the code refers to the right cell for teh course\new worksheet name.


    LVL 18

    Expert Comment


    You just need to give the filter column, rest the code will handle.

    Author Closing Comment

    Brilliant! Much appreciated...


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 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

    23 Experts available now in Live!

    Get 1:1 Help Now