Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link table from another sheet (similar to Paste Li (2003)

    I want my Excel file to have 2 sheets with 2 tables.

    I want the 2nd Sheet

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link table from another sheet (similar to Paste Li (2003)

    Yes, but if you need details on how to do it, you will have to provide details on what you need since there are many ways to do it.

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table from another sheet (similar to Paste Li (2003)

    Attached is an example file demonstrating what I need.

    Main sheet has list of all Students.

    I Copied and Pasted table from main sheet into

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link table from another sheet (similar to Paste Li (2003)

    I would not recommend doing this. Why do you need duplicate copies of the same data?

    If you add an autofilter to the master data, you can filter on Male/Female, and get a display (to print for example) of either male of female.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link table from another sheet (similar to Paste Li (2003)

    If you really need to keep multiple copies (which again I do not think you should do!), 1 relatively easy way is to copy them from the master when you activate the sheets:

    Add this function to a general module:
    <pre>Option Explicit
    Function ReplaceCopyFilter(sFilter As String)
    Dim wks As Worksheet
    Dim rMaster As Range
    Set wks = ActiveSheet
    wks.Range("A4:G65536").Clear
    With Worksheets("Master DB")
    Set rMaster = .Range(.Range("a4"), _
    .Range("G65536").End(xlUp))
    End With
    With rMaster
    .AutoFilter Field:=3, _
    Criteria1:=sFilter
    .Copy wks.Range("a4")
    .AutoFilter
    End With
    Set wks = Nothing
    Set rMaster = Nothing
    End Function</pre>


    Then in the code for the Male sheet add this code:
    <pre>Option Explicit
    Private Sub Worksheet_Activate()
    Call Module1.ReplaceCopyFilter("M")
    End Sub</pre>


    Then in the code for the Female sheet add this code:
    <pre>Option Explicit
    Private Sub Worksheet_Activate()
    Call Module1.ReplaceCopyFilter("F")
    End Sub</pre>


    Now whenever you activate the male or female sheet, it will clear the database on that sheet, and copy the filtered data to that sheet. This ensures whenever you activate the sheets they will have the current extract of the data.

    Steve

  6. #6
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table from another sheet (similar to Paste Li (2003)

    Cheers Steve; this's good.

    Please tell me best way to do that AutoFilter. How would I do it on a complex sheet with many different kinds of fields?

    Bob.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link table from another sheet (similar to Paste Li (2003)

    I am not sure what you are asking exactly. The code just creates a subset of the original database. As I stated I don't recommend keeping multiple copies. It is an inefficient use of resources.

    The function filters on column 3 given a value to filter on. You could make the function more general by giving the column and the string.

    MS MVP Debra Dagliesh has some code for Excel Filters -- AutoFilter Programming which may help you out. If you have specific questions you will need to elaborate.

    Autofilterings "strength" in my opinion, is that it does not have to be used in code. It is an intuitive way to just have the user filter the data without needed to create separate subsets of the data.

    I would recommend:
    gettting rid of all the code and the 2 separate sheets (only have the "master db")
    Select a cell in the datarange
    Data-Autofilter

    Now you can select from the picklist how you want the data displayed with no coding...

    Steve

Posting Permissions

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