Solved

Filter rows into another worksheet

Posted on 2012-04-05
19
433 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
  • 11
  • 8
19 Comments
 
LVL 41

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 41

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
 

Author Comment

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

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 41

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 41

Accepted Solution

by:
dlmille earned 500 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

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 41

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 41

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now