I'm hoping someone can help me with this unique situation. I need to create a workbook where the user can enter names into sheet1 like the following:

Smith, Carol
Jones, Ted
Jackson, Abigail
Anderson, Kim
Kirby, Stan

When the person clicks on sheet2 those same names should exist in column A **sorted alphabetically**. The user should not have to perform a sort by clicking a button, selecting a range, etc. The list should already be sorted. Can someone tell me how I would accomplish this? Many thanks.

Hi,

Am Sat, 21 Oct 2017 08:06:19 -0700 (PDT) schrieb :

I'm hoping someone can help me with this unique situation. I need to create a workbook where the user can enter names into sheet1 like the following:

Smith, Carol
Jones, Ted
Jackson, Abigail
Anderson, Kim
Kirby, Stan

When the person clicks on sheet2 those same names should exist in column A **sorted alphabetically**. The user should not have to perform a sort by clicking a button, selecting a range, etc. The list should already be sorted. Can someone tell me how I would accomplish this? Many thanks.

try in the code module of sheet2:

Private Sub Worksheet_Activate()
Dim LRow As Long

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With Sheets("Sheet2")
.Range("A1").Resize(LRow).Value = _
Sheets("Sheet1").Range("A1:A" & LRow).Value
.UsedRange.Sort Key1:=Range("A1"), order1:=xlAscending, Header:=xlNo
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Sorry, forgot to add in my description that there is a header in cell A1. Sorry. How would your code look with this change? Thank you so much.

Hi,

Am Sat, 21 Oct 2017 08:39:00 -0700 (PDT) schrieb :

Sorry, forgot to add in my description that there is a header in cell A1. Sorry. How would your code look with this change? Thank you so much.

then try:

Private Sub Worksheet_Activate()
Dim LRow As Long

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = False
With Sheets("Sheet2")
.Range("A1").Resize(LRow).Value = _
Sheets("Sheet1").Range("A1:A" & LRow).Value
.UsedRange.Sort Key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016