There is something unclear:
when you say " i have 2 worksheets, one with..." you mean 2 worksheets in the same workbook,
or, more probably , you meant " 2 workbooks ", the second workbook contains 50 worksheets named with states name?
Check the samples attached, ( you need to sort worksheets after updating values), for a macro to update all worksheets, you can try this: (will sort after column C-ID number)
Sub SortAllWks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A5:E175").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C5:C175"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A4:E175")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next ws
End Sub
All the work can be done with a macro, as an alternative..
|