Filter rows into another worksheet

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
RayneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
RayneAuthor Commented:
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
dlmilleCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

RayneAuthor Commented:
Thank you Dave :)
0
dlmilleCommented:
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
dlmilleCommented:
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
RayneAuthor Commented:
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
dlmilleCommented:
Ok - I created a ClearMain() routine and call it from CBInitialize which is called on workbook open.

Dave
CopyInfo-r2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RayneAuthor Commented:
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
dlmilleCommented:
That's what I thought I did.  A bit more testing and slight modification, apologies :P

See attached.

Dave
CopyInfo-r3.xlsm
0
RayneAuthor Commented:
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
RayneAuthor Commented:
That is a Breeze. Awesome. That is a perfect thing I could think of :)
Thank you Sir
0
dlmilleCommented:
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
RayneAuthor Commented:
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
RayneAuthor Commented:
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
RayneAuthor Commented:
Dave,

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

Thank you
R
0
dlmilleCommented:
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
RayneAuthor Commented:
Sure Dave
0
RayneAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.