Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Sort Ascending after filter.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 90
Default Sort Ascending after filter.
I have a worksheet that has A:L columns. I have several command buttons that
filter the data diffrent ways. I want to put a vb command in each that will
sort ascending on column A based on the rows remaining after the filter. Of
course the amount of records will change each time based on the filter. How
do I build a range for this? I am not sure how to build the code for the sort
at all. Please Help!

Here is my code:

Option Explicit

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band3_Click()
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

Private Sub Band4_Click()
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 4"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band5_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 5"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Manager_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Manager"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Military_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Military"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub ReportPreview_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Sort Ascending after filter.
Maybe you could just sort the unfiltered range.

Can you pick out a column that always has data in it when that row is used.
Your worksheet_change event makes it look like you can leave column A empty--so
you'll have to change this code to your column of choice.

Put this code in the same worksheet module:

Option Explicit
Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

With .Range("a1:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom

End With
End With
Application.EnableEvents = True

End Sub

And now you have a decision to make--where to sort the data--in the
worksheet_Change event or from each of the buttons.

If you use the worksheet_change event, call it before the End sub:

....
Call SortThisSheet
End sub

If you put it in the sort button click events, call it before you do any
filters.

Private Sub ActiveRecords_Click()
Call SortThisSheet
'rest of code...

Kenny wrote:

I have a worksheet that has A:L columns. I have several command buttons that
filter the data diffrent ways. I want to put a vb command in each that will
sort ascending on column A based on the rows remaining after the filter. Of
course the amount of records will change each time based on the filter. How
do I build a range for this? I am not sure how to build the code for the sort
at all. Please Help!

Here is my code:

Option Explicit

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band3_Click()
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

Private Sub Band4_Click()
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 4"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band5_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 5"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Manager_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Manager"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Military_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Military"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub ReportPreview_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 90
Default Sort Ascending after filter.
Thanks Dave I am getting an error with this code, something about mergin
cells. Also I tried fixing that area wich did not work. I am going to start
at a5 down and over to l for the sort. Whats happening

Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a6:L" & LastRow)
.Cells.Sort Key1:=.Columns(0), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True

End Sub






'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
AutoFilterMode = False
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="

End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 4"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 5"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Manager"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Military"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub





"Dave Peterson" wrote:

Maybe you could just sort the unfiltered range.

Can you pick out a column that always has data in it when that row is used.
Your worksheet_change event makes it look like you can leave column A empty--so
you'll have to change this code to your column of choice.

Put this code in the same worksheet module:

Option Explicit
Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

With .Range("a1:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom

End With
End With
Application.EnableEvents = True

End Sub

And now you have a decision to make--where to sort the data--in the
worksheet_Change event or from each of the buttons.

If you use the worksheet_change event, call it before the End sub:

....
Call SortThisSheet
End sub

If you put it in the sort button click events, call it before you do any
filters.

Private Sub ActiveRecords_Click()
Call SortThisSheet
'rest of code...

Kenny wrote:

I have a worksheet that has A:L columns. I have several command buttons that
filter the data diffrent ways. I want to put a vb command in each that will
sort ascending on column A based on the rows remaining after the filter. Of
course the amount of records will change each time based on the filter. How
do I build a range for this? I am not sure how to build the code for the sort
at all. Please Help!

Here is my code:

Option Explicit

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band3_Click()
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

Private Sub Band4_Click()
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 4"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band5_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 5"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Manager_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Manager"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Military_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Military"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub ReportPreview_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Sort Ascending after filter.
Merged cells will cause errors with sorts.

That's one reason I try not to use them.

If you want to remove them,
Select the range
format|Cells|alignment tab|Uncheck Merge cells
(xl2003 menu system)

Kenny wrote:

Thanks Dave I am getting an error with this code, something about mergin
cells. Also I tried fixing that area wich did not work. I am going to start
at a5 down and over to l for the sort. Whats happening

Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a6:L" & LastRow)
.Cells.Sort Key1:=.Columns(0), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True

End Sub

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
AutoFilterMode = False
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'Application.ScreenUpdating = True
End Sub

Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="

End Sub

Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 4"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 5"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Manager"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Military"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

"Dave Peterson" wrote:

Maybe you could just sort the unfiltered range.

Can you pick out a column that always has data in it when that row is used.
Your worksheet_change event makes it look like you can leave column A empty--so
you'll have to change this code to your column of choice.

Put this code in the same worksheet module:

Option Explicit
Sub SortThisSheet()

Dim LastRow As Long

Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

With .Range("a1:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom

End With
End With
Application.EnableEvents = True

End Sub

And now you have a decision to make--where to sort the data--in the
worksheet_Change event or from each of the buttons.

If you use the worksheet_change event, call it before the End sub:

....
Call SortThisSheet
End sub

If you put it in the sort button click events, call it before you do any
filters.

Private Sub ActiveRecords_Click()
Call SortThisSheet
'rest of code...

Kenny wrote:

I have a worksheet that has A:L columns. I have several command buttons that
filter the data diffrent ways. I want to put a vb command in each that will
sort ascending on column A based on the rows remaining after the filter. Of
course the amount of records will change each time based on the filter. How
do I build a range for this? I am not sure how to build the code for the sort
at all. Please Help!

Here is my code:

Option Explicit

'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With

'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If

End Sub

Private Sub ActiveRecords_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band3_Click()
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

Private Sub Band4_Click()
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 4"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Band5_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 5"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Manager_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Manager"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub Military_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Military"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
End Sub

Private Sub ReportPreview_Click()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
End Sub

Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A2").AutoFilter Field:=3
ActiveSheet.Range("A2").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub

--

Dave Peterson


--

Dave Peterson
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
Sort Ascending Using Auto-Filter Ignores First Row jgraves Excel Discussion (Misc queries) 1 July 26th 07 02:14 PM
Ascending Sort formula, change to neg #: descending sort.. nastech Excel Discussion (Misc queries) 6 July 2nd 07 11:00 PM
sort ascending error blefevre Excel Discussion (Misc queries) 2 August 20th 06 04:42 PM
sort ascending tab is not active. Why? jeremy corke Excel Worksheet Functions 2 July 10th 05 12:00 AM
how can I hide sort ascending and sort descending options in the . vida Excel Discussion (Misc queries) 0 December 11th 04 12:31 AM


All times are GMT +1. The time now is 10:30 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