sotring worksheets in a workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with 4 worksheets. The “name†column is linked from SHEET1
to the “name†column in SHEET2, SHEET3 and SHEET4. I need to be able to
change a name in SHEET1 and sort all of the data colunms in all 4 sheets by
“name.†Any ideas on how to do this?
 
here are a couple to try
Sub sortworksheets()
Dim Cnt As Integer
Dim n As Integer
Dim m As Integer
Dim WS As Worksheet
Set WS = ActiveSheet
Cnt = ActiveWorkbook.Worksheets.COUNT
For m = 1 To Cnt
For n = m To Cnt
If UCase(Worksheets(n).Name) < UCase(Worksheets(m).Name) Then
Worksheets(n).Move Before:=Worksheets(m)
End If
Next n
Next m
WS.Activate
End Sub
Sub sortworksheetsA()
Dim m As Integer
On Error GoTo EndOfMacro
Application.ScreenUpdating = False
Cnt = ActiveWorkbook.Worksheets.COUNT
For m = 1 To Cnt
For n = m To Cnt
If UCase(Worksheets(n).Name) < UCase(Worksheets(m).Name) Then
Worksheets(n).Move Before:=Worksheets(m)
End If
Next n
Next m
EndOfMacro:
Application.ScreenUpdating = True
End Sub
 
Thanks Don, but what I need to do is sort the data within the worksheets, not
sort the order of the worksheets.
 
Pineywoods,

If I'm understanding correctly, maybe this will work . . .

Assuming:

(1a) Four sheets named Sheet1, Sheet2, Sheet3, and Sheet4.
(1b) Each sheet has a title in cell A1 of each sheet called “Nameâ€.
(1c) The database covers the range A2:A6 in each sheet.
(1d) You make changes to the data in Sheet1. The data in Sheet2, Sheet3,
and Sheet4 is tied back to Sheet1.

(2) Add the following code to the Sheet1 code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
If Not Application.Intersect(Target, Range("A2:A6")) Is Nothing Then
Call SortSheets
End If
End Sub

(3) Create a new regular code module and add this code:

Sub SortSheets()
Sheets("Sheet1").Select
Call Sort
Sheets("Sheet2").Select
Call Sort
Sheets("Sheet3").Select
Call Sort
Sheets("Sheet4").Select
Call Sort
Sheets("Sheet1").Select
Range("A1").Select
End Sub

Sub Sort()
Range("A1").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess
Range("A1").Select
End Sub

Whenever you make a change to a cell in Sheet1 Range A2:A6, the first macro
triggers the SortSheets routine. The SortSheets routine selects a sheets and
runs the Sort routine.
 
Back
Top