?
Solved

Filter rows into another worksheet

Posted on 2012-04-05
19
Medium Priority
?
442 Views
Last Modified: 2012-06-26
Hello All,

I have a issue.
All raw content is on the source worksheet.
What I want: When I click on the checkboxes on the Main View sheet, the filtered information basically shows up in the mainviewsheet. I think a copy feature can be used or I am not sure how to accomplish this via VBA.

Thank you
CopyInfo.xlsx
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
19 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37814508
You are correct, this can be done and I'm working it.

A couple questions:

1.  Any reason you don't just use Data or Advanced Filter on the Source sheet to select the fields you want?

2.  Going forward with your request, I assume you want the Main View to show results and you check/uncheck the boxes, correct?

Dave
0
 

Author Comment

by:Rayne
ID: 37814572
Thank you Dave,
You are always quick to help.

Any reason you don't just use Data or Advanced Filter on the Source sheet to select the fields you want?
I want to have a separate worksheet that just shows the records I want filtered from the source list. Reasoning: keep the main list away from the view of others and have them view only what they want to see. Not more


.Going forward with your request, I assume you want the Main View to show results and you check/uncheck the boxes, correct?
Absolutely, yes. So if the user unchecks/ check something on the Main view, it basically filters the stored information from the source list and outputs it in the main view.
rayne
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37814575
Great.  I was looking at a past solution and sure enough it was for you - so it will work similarly, with the caveats you stated - re: pull results into Main sheet.

Cheers,

Dave
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Rayne
ID: 37814597
Thank you Dave :)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37814618
Quick question - if you don't select anything for a parameter, is that the same as selecting all of them? so if someone unchecks all, treat that as checking all?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37814670
Unlike the prior solution, rather than using a data filter and copying that filter over, I thought this approach would be as effective, is less sophisticated then building the data filter which is also doable, and uses the advanced filter, instead.  That way, perhaps, you may find it easier to use this code as a starting point for new projects.  The way it works:

1.  There are 3 criteria setup for the advanced filter (see named range:  Criteria_Range)
2.  There are also 3 ranges (see Size_Range, P123_Range, and M123_Range)
3.  When a checkbox is checked the 2nd column of the respective XXX_Range is updated TRUE/FALSE based on what's selected
4.  The autofilter criteria formula uses vlookup to determine whether the selected values are in the table being queried.  If all options are ZERO (unchecked) a TRUE is returned as well, so if the user doesn't check any box for that criteria, its as if all checkboxes for that criteria are selected.
5.  Once the autofilter is run, the data is copied to a temporary area on the same sheet (as autofilter wants to keep results on the same sheet)
6.  The macro then copies that data to the main sheet, overwriting prior results.

Here's the code:

Option Explicit
Sub CB_M1_Click()
    Call doFilterGetResults
End Sub
Sub CB_M2_Click()
    Call doFilterGetResults
End Sub
Sub CB_M3_Click()
    Call doFilterGetResults
End Sub
Sub CB_P1_Click()
    Call doFilterGetResults
End Sub
Sub CB_P2_Click()
    Call doFilterGetResults
End Sub
Sub CB_P3_Click()
    Call doFilterGetResults
End Sub
Sub CB_Small_Click()
    Call doFilterGetResults
End Sub
Sub CB_Medium_Click()
    Call doFilterGetResults
End Sub
Sub CB_Large_Click()
    Call doFilterGetResults
End Sub
Sub CBInitialize(wks As Worksheet)
Dim cBox As CheckBox

    'initialize all CheckBoxes to TRUE
    For Each cBox In wks.CheckBoxes
        If Left(UCase(cBox.Name), 2) = "CB" Then
            cBox.Value = True
        End If
    Next cBox

    Call doFilterGetResults
End Sub
Sub doFilterGetResults()
Dim wkb As Workbook
Dim wksMain As Worksheet
Dim wksSource As Worksheet
Dim rChange As Range
Dim cBox As CheckBox
Dim strTmp As String
Dim lIdx As Long
Dim rClear As Range
Dim rLastSel As Range

Application.ScreenUpdating = False

    Set wkb = ThisWorkbook
    Set wksMain = wkb.Worksheets("MainViewSheet")
    Set wksSource = wkb.Worksheets("Source")

    Set rLastSel = ActiveCell
    
    For Each cBox In wksMain.CheckBoxes
        
            'check box name CB_XXX directly indicates what the selection is (e.g., XXX)
            strTmp = Right(cBox.Name, Len(cBox.Name) - 3)
            Select Case strTmp
                Case "M1", "M2", "M3":
                    Set rChange = [M123_Range].Cells(Right(strTmp, 1), 2)
                Case "P1", "P2", "P3":
                    Set rChange = [p123_range].Cells(Right(strTmp, 1), 2)
                Case "Small", "Medium", "Large":
                    If strTmp = "Small" Then
                        lIdx = 1
                    ElseIf strTmp = "Medium" Then
                        lIdx = 2
                    Else
                        lIdx = 3
                    End If
                    Set rChange = [Size_Range].Cells(lIdx, 2)
                Case Else
                    'potentially flag error?  or could be other controls we don't need to mess with
            End Select
            If Not rChange Is Nothing Then rChange.Value = IIf(cBox.Value = 1, 1, 0)
            Set rChange = Nothing
    Next cBox
    
    'execute Advanced filter
    [SourceRange].AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[CriteriaRange], CopyToRange:=[DestRange], Unique:=False
        
    'clear main sheet results
    Set rClear = [MainResults]
    wksMain.Range(rClear, wksMain.Cells(wksMain.Cells(wksMain.Rows.Count, rClear.Cells(1, 1).Column).End(xlUp).Row, rClear.Cells(1, 1).Column + rClear.Columns.Count - 1)).Clear
    
    'copy results to main sheet
    [DestResults].Copy
    [MainResults].PasteSpecial
    Application.CutCopyMode = False
    
    rLastSel.Select
    
Application.ScreenUpdating = True

End Sub

Function addStr(myStr As String, myUpdate As String) As String
    If addStr = vbNullString Then
        addStr = myUpdate
    Else
        addStr = addStr & "," & myUpdate
    End If
End Function

Open in new window


in ThisWorkbook - to set user to MainViewSheet and check all checkboxes on Open:
Option Explicit

Private Sub Workbook_Open()
    ThisWorkbook.Sheets("MainViewSheet").Activate
    Call CBInitialize(ThisWorkbook.Worksheets("MainViewSheet"))
End Sub

Open in new window

See attached.  I renamed the check boxes to:  CB_M1 thru CB_M3, CB_P1 thru CB_P3, and CB_Small through CB_Large.

If you ever have LOTS of check boxes, then you can ask a question to simplify all the little stubs for clicking each checkbox.  Its possible to setup an Application Event to handle this, such that if new checkboxes are created, then they can be processed (run the same filter - adding the code for them) without having to add new stubs.

Enjoy!

Dave
CopyInfo-r1.xlsm
0
 

Author Comment

by:Rayne
ID: 37814939
Awesome Dave, this is Perfect! One thing though, is there a way hide the rows at the beginning when I open the workbook. Like when I open the workbook, the main view looks empty except for the top named column headings default view) . And then moment I click on any one of the check boxes, it  shows it in the mainview worksheet?

Right now, it shows up all the unfiltered data in the MainView when I open the workbook and before clicking any checkboxes...does that answer your previous question? Let me know if we are not on the same page.

Thank You
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37814948
Ok - I created a ClearMain() routine and call it from CBInitialize which is called on workbook open.

Dave
CopyInfo-r2.xlsm
0
 

Author Comment

by:Rayne
ID: 37814958
Thank you Dave, that is great. One thing - is it possible to keep the named column (5th row) at least but without the data? in the mainview on the startup of the file

If thats a hassle or a more complex thing  - thats fine but if thats possible easily then let me know.

Rayne
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37814963
That's what I thought I did.  A bit more testing and slight modification, apologies :P

See attached.

Dave
CopyInfo-r3.xlsm
0
 

Author Closing Comment

by:Rayne
ID: 37814973
Dave,

Let me know if you think that column is achievable easily. If not, don't worry about it. Thanks again for your kind help.

Rayne
0
 

Author Comment

by:Rayne
ID: 37814977
That is a Breeze. Awesome. That is a perfect thing I could think of :)
Thank you Sir
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37814978
Thanks.  I just cleared from the range name offset by 1 row, rather than right at the range name, thus keeping the column headers on the first row of the range.

Dave
0
 

Author Comment

by:Rayne
ID: 37814988
gotcha....and thanks for really explaining and summarizing the way you went with this design. I think it really helps to grasp it quickly :)
0
 

Author Comment

by:Rayne
ID: 37822158
Hi Dave,

The steps to add a new filter item
1.      Add it to the range type
2.      Ensure to add it to the column in the main table
3.      Update VBA like adding a new line to it
I tried doing all this to add [P4] and [ExtraLarge] filters to the list but it won’t filter. If you could let me know what changes I need to make to add new items to the filter lists that would awesome. As more new filter will be added so. I have added the file that I worked on.

Regards
R
CopyInfo-r4-new-filters.xlsm
0
 

Author Comment

by:Rayne
ID: 37822161
Dave,

Let me know if I need to do a new question for this follow up or what is appropriate.

Thank you
R
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37822164
Yes, please post a new question and specify what you specified in the post.  I'll post a response and also give explanation for adding new fields to the filter.

Dave
0
 

Author Comment

by:Rayne
ID: 37822214
Sure Dave
0
 

Author Comment

by:Rayne
ID: 38127546
Hello Dave,

I have posted another question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27771258.html#a38127538

look at it when you are free
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

765 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