Have data across 2 sheets/ auto sorting

[Will321988]

Hi,

Could someone please advise me on how I would have the same data across two sheets within the same excel document? I have a table of 1000 clients, what I am wanting to do is only see the client at the top of the list (Rows 1 and 2) on sheet 2. I mainly sort this data through ‘next contact date’ so when I change the date on either sheet, how can I do it so it will automatically change on the other sheet?

Furthermore, if i can now see the top contact on sheet 2 only and I changed the ‘next contact date’ meaning it has automatically changed on sheet 1, how do I set it that it drops down the list automatically and therefore a new client appears on sheet 2?

Any help is much welcomed.

Thanks.
Will

 

[pbornemeier]

Fully automated stuff can get annoying, but here is what I think you are asking for. Test on a copy of your data

Assuming :
Sheet1 is the name of the worksheet that has the table and the table is really a ListObject
Sheet2 is the name of the worksheet with one name
The layout on both worksheets with the first column holding a name and the second column holding a date

Option Explicit

'This code to the ThisWorkbook code page

Private Sub Workbook_Open()
    Application.EnableEvents = True
End Sub

Option Explicit

'Place this code on the codepage of the Sheet1, the
'  worksheet that holds the table with all contacts

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("tblList[[#All],[Date]]")) Is Nothing Then
        'A value in the date column was changed
        
        'Sort the table by date and name
        SortListTable
        'Turn off event tracking to avoid infinite loop
        Application.EnableEvents = False
        'Copy Header and First Row of list to Sheet2
        Intersect(Range("tblList"), Rows("1:1")).Copy Destination:=Worksheets("Sheet2").Range("A2")
        'Turn on event tracking
        Application.EnableEvents = True

    End If
End Sub

Sub SortListTable()
    With Me 'Reference Sheet1
        .ListObjects("tblList").Sort.SortFields.Clear
        .ListObjects("tblList").Sort.SortFields.Add _
            Key:=Range("tblList[Date]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .ListObjects("tblList").Sort.SortFields.Add _
            Key:=Range("tblList[Name]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .ListObjects("tblList").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Option Explicit

'This code should be placed on the code page of Sheet2, the sheet that holds the single name
Private Sub Worksheet_Activate()
    
    'Turn off event tracking to avoid infinite loop
    Application.EnableEvents = False
    'Copy Header and First Row of list to Sheet2
    Worksheets("Sheet1").ListObjects(1).HeaderRowRange.Copy Destination:=Worksheets("Sheet2").Range("A1")
    Worksheets("Sheet1").ListObjects(1).DataBodyRange.Rows(1).Copy Destination:=Worksheets("Sheet2").Range("A2")
    'Turn on event tracking
    Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'If the date in B2 is changed then update Be in Sheet1
    '  and sort table then copy new top values to row2
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        'B2 was changed
        
        'Turn off event tracking to avoid infinite loop
        Application.EnableEvents = False
        'Change the date value of row 2 in Sheet2
        Worksheets("Sheet1").Range("B2").Value = Target.Value
        'Sort list
        Worksheets("Sheet1").SortListTable
        'Copy new top row to Sheet2
        Worksheets("Sheet1").ListObjects(1).HeaderRowRange.Copy Destination:=Worksheets("Sheet2").Range("A1")
        Worksheets("Sheet1").ListObjects(1).DataBodyRange.Rows(1).Copy Destination:=Worksheets("Sheet2").Range("A2")
        'Turn on event tracking
        Application.EnableEvents = True
        
    End If
End Sub