Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > sort between a group of sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default sort between a group of sheets
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9,101
Default sort between a group of sheets
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" wrote:

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default sort between a group of sheets
The code still works its the total sheet that is the problem, I do not want
to include the total sheet.

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9,101
Default sort between a group of sheets
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" wrote:

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

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default sort between a group of sheets
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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9,101
Default sort between a group of sheets
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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default sort between a group of sheets
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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9,101
Default sort between a group of sheets
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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 26
Default sort between a group of sheets
thanks for your help, it works great.

"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Group cells during sort John Boyee Excel Discussion (Misc queries) 1 August 25th 08 04:48 PM
Group sort James Excel Discussion (Misc queries) 2 September 5th 07 11:22 PM
Group & Sort A Kelly[_2_] Excel Discussion (Misc queries) 1 August 28th 07 01:58 PM
sort a group mcblts Excel Discussion (Misc queries) 2 July 14th 07 08:42 AM
Sort by group DexterV New Users to Excel 4 July 8th 05 01:48 PM


All times are GMT +1. The time now is 06:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement