• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

filtered view on separate worksheets

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
Needy11
Asked:
Needy11
  • 3
  • 2
1 Solution
 
krishnakrkcCommented:
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
 
Needy11Author Commented:
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
 
Needy11Author Commented:
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
 
krishnakrkcCommented:
Hi,

You just need to give the filter column, rest the code will handle.
0
 
Needy11Author Commented:
Brilliant! Much appreciated...

Ciaran
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now