Data entry on Sheet 1 that automatically updates a sorted sheet 2

[RICKVP20]

I have a main sheet (Sheet 1) for employee data entry based on a sequential number and I have a copy of that sheet (Sheet 2), using VBA code, which sorts automatically by last name.

I'd like to make the data entry only one time on Sheet 1 and have the data update to Sheet 2 which would sort automatically.

The VBA code I used to sort Sheet 2 automatically was obtained from doing various searches and it works great.

 

[ranman256]

this code copies 3 columns, ID, FIRSTN, LASTN
then sorts on LASTN, FIRSTN

Sub CopyDataAndSort()
Dim r As Long


    Sheets("sheet2").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("sheet1").Select
    Columns("A:C").Select
    Selection.Copy
    Sheets("sheet2").Select
    ActiveSheet.Paste
    r = ActiveSheet.UsedRange.Rows.Count
    
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("sheet2").Sort.SortFields.Clear
       'sort 1 on LAST NAME
    ActiveWorkbook.Worksheets("sheet2").Sort.SortFields.Add Key:=Range("C2:C" & r), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       'sort 2 on FIRST NAME
    ActiveWorkbook.Worksheets("sheet2").Sort.SortFields.Add Key:=Range("B2:B" & r), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet2").Sort
        .SetRange Range("A1:C" & r)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

 

[RICKVP20]

Thanks for the reply. I just got back to my office and will try it out at the first opportunity. Thanks again.

 

[RICKVP20]

ranman256,
I copied the code and tried it out, but I am a novice in VBA. I constructed a test workbook with three columns and populated them with some data. I'm not sure which part gets the code, Sheet 1, Sheet 2, both or just the Workbook? Do I need to copy Sheet 1 to Sheet 2 manually and then run a macro? Or, will it populate and sort Sheet 2 automatically upon data entry in Sheet 1?