sort between a group of sheets

[tkincaid]

I had this working until I had to add a total sheet, I'm trying to sort
between the following sheets A80 and ZTL. I can't figure out where or what
to change to get this to work.

Sub SortbyDate()
Dim sh
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Sheets

With sh
.Range("B3:J97").Sort Key1:=.Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next
Application.ScreenUpdating = True

End Sub

 

[Joel]

I think the code is still working. I think your problem is on the total
sheet and not the other sheets. You probably have formulas on the total
sheet that reference the other sheets and when the sort is performed the
total sheet is getting updated incorrectly. Also you code will also sort the
total sheet as well as all the other sheets. do you want the total sheet
sorted?

 

[tkincaid]

The code still works its the total sheet that is the problem, I do not want
to include the total sheet.

 

[Joel]

Sub SortbyDate()
Dim sh
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Sheets
if sht.name <> "total" then
With sh
.Range("B3:J97").Sort Key1:=.Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
end if
Next
Application.ScreenUpdating = True

End Sub

 

[tkincaid]

Joel

Thanks for the help, but I can't get it to work, so I will try to explain
this a little better. I added the total sheet at the end of all my sheets,
which I have labeled. The labels range from A80 to ZTL then the total sheet.
I need this to work on all sheets except the total sheet. I hope this is
explained better.

 

[Joel]

I had sht instead of sh. Make surre the sheet name is spelled exactly like
the tab on the worksheet including any capital letters in the sheet name.


Sub SortbyDate()
Dim sh
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "total" Then
With sh
.Range("B3:J97").sort Key1:=.Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End If
Next
Application.ScreenUpdating = True

End Sub

 

[tkincaid]

Thansk Joel it worked perfect, if I decide to add a sum total sheet do I just
put it in quotes next to total.

 

[Joel]

Sub SortbyDate()
Dim sh
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "total" and _
sh.Name <> "sum total" Then
With sh
.Range("B3:J97").sort Key1:=.Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End If
Next
Application.ScreenUpdating = True

End Sub

 

[tkincaid]

thanks for your help, it works great.

Sub SortbyDate()
Dim sh
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "total" and _
sh.Name <> "sum total" Then
With sh
.Range("B3:J97").sort Key1:=.Range("B3"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End If
Next
Application.ScreenUpdating = True

End Sub