How to do an auto sort on sheet2
 

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.

How to do an auto sort on sheet2
 

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

How to do an auto sort on sheet2
 

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.

How to do an auto sort on sheet2
 

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