K
Kenny
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
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