?
Solved

filtered view on separate worksheets

Posted on 2012-09-05
5
Medium Priority
?
511 Views
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.

Ciaran
samplecolumns.xlsx
0
Comment
Question by:Needy11
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38367383
Hi

Try

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
        Next
        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
            Next
        End If
    End With
    ShtData.Cells(1, r.Columns.Count + 2).Resize(2).ClearContents
                
End Sub

Open in new window


Kris
0
 

Author Comment

by:Needy11
ID: 38368676
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

Ciaran

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?

tx

Ciaran
0
 

Author Comment

by:Needy11
ID: 38368758
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.

tx

Ciaran
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38368897
Hi,

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

Author Closing Comment

by:Needy11
ID: 38371376
Brilliant! Much appreciated...

Ciaran
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 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