Nuke_It_Newport
New Member
- Joined
- Nov 17, 2020
- Messages
- 47
- Office Version
- 365
- Platform
- Windows
Good morning everyone-
I need some help sorting tables in the following order. I need to perform this sort on any worksheet in the workbook. All the sheets have the same table with headers, same column names, different table names.
First sort is as follows:
Second sort is as follows:
I have the following code, and it sorts correctly on the table referenced in the code, but I don't know how to rewrite it to sort on the active sheet's table.
I have changed this line:
To this:
This references the table correctly.
I can't figure out how to change the following lines to reference the active sheet's table, instead of "tblImport".
On another note, I read somewhere that it's not a preferred method to sort based on font color, but to sort on a helper column instead. I will change this once I get my current issue resolved.
Thanks!
Chad
I need some help sorting tables in the following order. I need to perform this sort on any worksheet in the workbook. All the sheets have the same table with headers, same column names, different table names.
First sort is as follows:
Operating Area (Ascending)
Activity (Ascending)
Job Plan (Ascending)
Actual Finish (Ascending)
Work Order (Ascending)
Second sort is as follows:
Work Order (RGB(0, 0, 0) "Black"
Work Order RGB(255, 102, 0) "Orange"
Work Order RGB(0, 128, 0) "Green"
Work Order RGB(255, 0, 0) "Red"
Work Order RGB(255, 0, 0) "Red"
I have the following code, and it sorts correctly on the table referenced in the code, but I don't know how to rewrite it to sort on the active sheet's table.
VBA Code:
Option Explicit
Sub SortImport()
Dim wb As Workbook
Dim ws As Worksheet
Dim listObj As ListObject
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set listObj = wb.Worksheets("Import").ListObjects("tblImport")
With wb
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Clear
With listObj.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("tblImport[Operating Area]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Activity]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Job Plan]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Actual Finish]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Work Order]"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Add(Range("tblImport[[#All],[Work Order]]"), xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)
With .Worksheets("Import").ListObjects("tblImport").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Clear
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Add(Range("tblImport[[#All],[Work Order]]"), xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 102, 0)
With .Worksheets("Import").ListObjects("tblImport").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Clear
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Add(Range("tblImport[[#All],[Work Order]]"), xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(0, 128, 0)
With .Worksheets("Import").ListObjects("tblImport").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Clear
.Worksheets("Import").ListObjects("tblImport").Sort.SortFields. _
Add(Range("tblImport[[#All],[Work Order]]"), xlSortOnFontColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
With .Worksheets("Import").ListObjects("tblImport").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
I have changed this line:
Code:
Set listObj = wb.Worksheets("Import").ListObjects("tblImport")
Code:
Set listObj = ws.ListObjects(1)
I can't figure out how to change the following lines to reference the active sheet's table, instead of "tblImport".
Code:
With listObj.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("tblImport[Operating Area]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Activity]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Job Plan]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Actual Finish]"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("tblImport[Work Order]"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
On another note, I read somewhere that it's not a preferred method to sort based on font color, but to sort on a helper column instead. I will change this once I get my current issue resolved.
Thanks!
Chad