Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Selecting a range of data from another using a table in another worksheet

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Selecting a range of data from another using a table in another worksheet

    HI guys
    I am no expert on VBA and could really do with some help. I have data in a table on one worksheet and want to click on the result in a cell that is the performance of the bucket in the worksheet Overview and then for it to take me to the second worksheet, action tracker and alllow me to only see the cell range that I have asked for . For instance if i click on CH - Bespoke cell, where 1.2% is the intersection of the two, I want to view the range of rows that have issues in the action tracker. I hope this is clear enough for you to help, thanks in advance. Brgds Sean

    Overview of performance Sheet name "Overview"
    Target Performance Discipline Time / Resource Bespoke Equipment Unallocated
    Country
    AT 95.5% 93.8% 0.0% 0.8% 0.0% 0.4% 5.0%
    BE 86.0% 82.0% 0.0% 0.0% 0.0% 0.0% 18.0%
    BG 96.2% 96.0% 2.0% 0.0% 0.0% 0.0% 2.0%
    CH 97.7% 96.4% 0.0% 0.0% 1.2% 0.5% 1.9%
    CZ 76.8% 59.9% 0.0% 0.0% 4.0% 10.0% 26.1%
    DE 84.9% 79.0% 0.0% 0.5% 0.0% 5.4% 15.1%


    Action Tracker Sheet the same.

    ID Meeting Issue Date Description Performance bucket Country Depot Status Priority Due Date
    1 11/27/2017 Direct feeder EPCOS Bespoke processes AT GRZ In progress H 1/15/2018
    2 11/27/2017 very short time window between arrival and processing to connect on-time of dedicated truck ex customer Palfinger Time/Resource issues AT SZG In progress M 1/15/2018
    3 11/27/2017 issue with data transmission Equipment issues AT VIE In progress L 1/15/2018
    3 11/27/2017 Mettler Toledo non convey measuring arm is broken Equipment issues AT LNZ In progress L 1/15/2018
    4 11/27/2017 Several times CWC V Tronic was used on Inbound mode, due to this many export cons leave without RPP. Shipments coming with own printed labels from the sender did not pass any RPP at all before export. Discipline BG SOF In progress M 1/15/2018
    5 11/27/2017 Sender Account Nr. 000212240 (TNT Swiss Post SA) needs to exclude from the report as this
    account nr. will only used in case of return shipments for the LGI / Bally / Jimmy Choo customers.
    Bespoke processes CH LUG In progress M 1/15/2018

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,421
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting a range of data from another using a table in another worksheet

    With the assumption that data in both sheets for the examples shown begin in column A, you might be able to use this worksheet event code. The code would be installed in the Overview sheet.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    Dim fn As Range
        Set fn = Sheets("Action Tracker").Range("F:F").Find(sh1.Cells(Target.Row, 1).Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fAdr = fn.Address
                Do
                    If InStr(fn.Offset(, -1).Value, Cells(1, Target.Column).Value) > 0 Then
                        Sheets("Overview").UsedRange.Rows.Hidden = True
                        Sheets("Overview").Rows(fn.Row).Hidden = False
                        Sheets("Overview").Select
                        Exit Do
                    End If
                    fn = Sheets("Action Tracker").Range("F:F").FindNext(fn)
                Loop While fn.Address <> fAdr
            End If
    End Sub
    Since I am guessing at where on the sheets your data is located, you might need to modify some of the parameters in the code.
    Also, if the code works as is, you will need to unhide the rows on Action Tracker when you have finshed working the issue.
    Last edited by JLGWhiz; Dec 7th, 2017 at 07:31 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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