+ Reply to Thread
Results 1 to 2 of 2

Filter excel sheet rows based on value selected in combobox -VBA

  1. #1
    Registered User
    Join Date
    10-25-2019
    Location
    India
    MS-Off Ver
    1902
    Posts
    1

    Filter excel sheet rows based on value selected in combobox -VBA

    Hi All; Just one day into VBA and i am stuck into a little something that i could really use help on. Appreciate any/all headways.

    What I have: Data in 4 separate tabs in excel(say, A,B,C,D sheets) and a "Control Sheet" having combobox for the column say "Names"
    What I am trying to do: Once user selects a "Name" in combobox, data across all the A,B,C,D for "Names" columns must be checked. For the rows that match the Name, four specific columns(Address,Age,location,Phone) from every tab(these columns are named consistently across tabs) must be copied and pasted on the control sheet.

    This is what i have so far. Its halfway through what i need but i already have an error on this. Can anyone please help me meet my problem statement?
    Here "sample" is my control sheet

    Dim WS_Count As Integer
    Dim I As Integer, J As Integer

    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count -1 /* to avoid selecting control sheet*/

    With Worksheets("Sample")
    X = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

    ' Begin the loop.
    For I = 1 To WS_Count

    ' Insert your code here.
    ' The following line shows how to reference a sheet within
    ' the loop by displaying the worksheet name in a dialog box.
    sheetName = ActiveWorkbook.Worksheets(I).Name
    sheetRows = (ActiveWorkbook.Worksheets(I).Range("A" & Rows.Count).End(xlUp).Row)

    For J = 1 To sheetRows
    If ActiveWorkbook.Worksheets(I).Cells(J, 1).Value = ComboBox1.Value Then
    .Rows(J).Copy Destination:=Worksheets("Sample").Range("A" & X)
    X = X + 1
    End If
    Next J
    Next I
    End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,766

    Re: Filter excel sheet rows based on value selected in combobox -VBA

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    The second advice:

    Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

    3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

    To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1