Solved

# filtered view on separate worksheets

Posted on 2012-09-05
Medium Priority
511 Views
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
Question by:Needy11
• 3
• 2

LVL 18

Accepted Solution

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
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
``````

Kris
0

Author Comment

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

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

ID: 38368897
Hi,

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

Author Closing Comment

ID: 38371376
Brilliant! Much appreciated...

Ciaran
0

## Featured Post

Question has a verified solution.

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