Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    mountains, North Carolina, USA
    Posts
    64
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Large data set filtered to new tabs

    I have a spreadsheet with a large number of rows containing data. To help me better analyze the data I have set up a series of tabs within the excel file. What I'm looking for is a way to automatically pull data from the main sheet to the individual tabs.

    For example, if Column A Row 4 contains Jeff a way that will pull the entire row automatically into the Jeff tab.
    If column A Row 4 contains the word David, a way to pull that entire row of data into the David tab.

    The data needs to be copied, not moved.

    Right now I sort and copy paste but there must be an easier way.

    Any suggestions?

    Using Excel 2007.

    Larry P

  2. #2
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    You can use a pivot table for your data. Set column A as a filter, then Show Report Filter Pages. A different tab will be created for Jeff, David, etc.

    Meleia

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts

    Using VBA

    Larry,

    Here is a bit of code that will update each sheet when you select its tab. The main sheet is called "Main". When you select any sheet other than the "Main" sheet and the name in column 1 matches the sheet name, then the entire row will be copied over. Opening another sheet will do the same for that sheet.

    Place the following code in each of the sheet's module except for "Main" or whatever you call your main sheet.

    HTH,
    Maud

    Names1.PNG Names2.PNG

    Code:
    Private Sub Worksheet_Activate()
    'DECLARE AND ASSIGN VARIABLES
    Dim Sheetname As String
    Dim Row As Integer
    Dim I As Integer
    Sheetname = ActiveSheet.Name
    Row = 2
    '------------------------------------------------------------
    'LOOK FOR MATCH IN COL A AND ACTIVE SHEET
    Application.ScreenUpdating = False
    Cells.ClearContents  'CLEAR ACTIVE WORKSHEET
    With Worksheets("Main")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row 'FIND LAST ROW (MAIN)
    For I = 2 To LastRow
        LastCol = .Cells(I, Application.Columns.Count).End(xlToLeft).Column 'FIND LAST COL IN ACTIVE ROW (MAIN)
        If .Cells(I, 1).Value = Sheetname Then 'IF VALUE IN COL A EQUALS NAME OF ACTIVE SHEET THEN...
           'COPY CELLS IN ROW FROM MAIN TO ACTIVE SHEET
            For K = 1 To LastCol
                Cells(Row, K).Value = .Cells(I, K).Value
            Next K
            Row = Row + 1
        End If
    Next I
    Cells(1, 1).Select
    End With
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-07-11 at 17:48.

  4. #4
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    This is a great, time-saving solution. Glad I was browsing the site today. Thanks Maudibe!
    myers515

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    Nice job.
    You can copy the entire row in one go, rather than looping cell by cell in each column as per your code.
    This is a little faster.
    By copying the entire row, you can also copy cell formats, cell comments etc that may be relevant.
    Also, for large data sets, you can dramatically improve the speed by turning calcs of during this process.
    In addition, rather than duplicate all of the code on all of the sheets, you could just call a single routine.
    This means you only have a single routine to update, rather than having to update code on all sheets if a change is required.

    I have attached my version of your file.
    Once again, nice job.

    zeddy
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    Larry P (2013-07-14)

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Zed,

    You're right about calling a single routine. Wasn't thinking! Thanks guys
    Last edited by Maudibe; 2013-07-12 at 23:44.

  8. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    mountains, North Carolina, USA
    Posts
    64
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I appreciate the input. The VBA looks very interesting. Sadly for me, even simple VBA is a bit beyond my capabilities. But, the good news is I have a buddy who should be able to take this head start and accomplish the goal.

    Thanks for everyone who helped.

    Larry P

Posting Permissions

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