Sort Ascending after filter.

G

Guest

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
 
D

Dave Peterson

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...
 
G

Guest

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
 
D

Dave Peterson

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Enable Command Button Procedure 1
Simplify Command Button Procedure 2
Prevent Duplicate Records 21
Tagt Row When data copied to another workbook 2
Coping Dates from workbook to workbook 2
Hide ALL dropdowns on autofilter 15
Form If IsEmpty Syntax 4
Advanced Filter Error - I cannot spot my Error 3

Top