Excel VBA:  Copy data from one worksheet to another based on selected criteria in a combobox

Posted on 2012-09-07
Last Modified: 2012-09-07
Hi Experts,

The set up:
I have a worksheet which I'll refer to as "Snapshot" that has a combobox in cell "A2" using data validation with a list of Supervisors.  

Also in the "Snapshot" worksheet the 1st Row is the header "B1:Z1" who's naming convention matches my 2nd worksheet's (known as "Agent") header exactly accept the "Agent" worksheet's header row starts at "A1:Y1" (1st 3 columns contain = "A" Manager's Names, "B" Supervisor's Names, and "C" Agent's names respectively).

What I would like to do:  
Based on the selected criteria in the combobox of "Snapshot" I would like to be able to COPY data from "Agent" range "A2:Y500" and have the rows of data pasted into columns "B:Z" on "Snapshot".  

For example:  If on "Snapshot" supervisor "TestSup_A" is selected then I would like to find "TestSup_A" name in column "B" of the "Agent" worksheet and bring back all of his or her agent data to the "Snapshot" worksheet.  Bringing back "TestSup_A" Manager and Agent alignment as well something similar to an "index-match" formula.

Sample Workbook attached .

Thank you!  :)
Question by:"Abys" Wallace
    LVL 92

    Accepted Solution

    The following appears to be working for me.  It goes on the code module for the Snapshot worksheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim LastRSource As Long, LastRDest As Long
        Dim Source As Worksheet
        If Not Intersect(Target, Me.[a2]) Is Nothing Then
            Application.EnableEvents = False
            With Me
                LastRDest = .Cells(.Rows.Count, "b").End(xlUp).Row
                If LastRDest > 1 Then
                    With .Range("b2:z" & LastRDest)
                        .Interior.ColorIndex = xlColorIndexNone
                    End With
                End If
                Set Source = ThisWorkbook.Worksheets("Agent")
                With Source
                    LastRSource = .Cells(.Rows.Count, "a").End(xlUp).Row
                End With
                Source.Range("a1").AutoFilter 2, .[a2], xlAnd
                On Error Resume Next
                Source.Range("a2:y" & LastRSource).SpecialCells(xlCellTypeVisible).Copy .[b2]
                On Error GoTo 0
            End With
            Application.EnableEvents = True
        End If
    End Sub

    Open in new window


    Author Closing Comment

    by:"Abys" Wallace
    Thank you.. that worked perfectly ...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now