Copy column to new sheet and update
 

I have a few worksheets in an excel workbook.
In worksheet one, Column C there is a list of names sorted in descend spend
order.
This list of names is dynamic and will change.
In worksheet two, Column A is a list I manually copied from Column C in
wksht one and sorted with vba code (see below). Also in column B, is a pic
number that goes with column A name.
Basically, when worksheet one names change and updates, I want a new list of
sorted names in worksheet two Column A, but the column b will have the
corresponding pic number and be unchanged. I want a code to automate this
process so I don't have to copy and paste each time.

For example,
Col A Col B
CompanyAd Pic1
CompanyBo Pic2

If CompanyAp is added, CompanyBo and Pic2 will be moved down one row,
leaving a blank cell in Column B for CompanyAp.

CompanyAd Pic1
CompanyAp
CompanyBo Pic2


Any help is appreciated? thanks


Below is the code I use to sort in ascending order for Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Columns("A:B").Sort key1:=Range("A1"), header:=xlNo
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub