TrinColll
06-14-2013, 04:16 PM
Hi guys I'm trying to sort a bunch of columns that contain check boxes based on the values in row1. When I Sort this manually it works and row 1 sorts and its corresponding check boxes move with it. When I ran the code I created Row 1 sorted but none of the corresponding columns moved.
heres an example of what I want
UNSORTED
---------SOP2 SOP1 SOP4 SOP3
mike-------x-----x-----x
dave-------x----------x--------
SORTED
-----------SOP1 SOP2 SOP3 SOP4
mike--------x----x----------x
dave-------------x----------x
I recorded a macro of me correctly sorting it manually
Sub Macro1()
'
' Macro1 Macro
'
'
Range("B1:W10").Select
ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Sort.SortFields.Add Key:=Range( _
"B1:W1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Sort
.SetRange Range("B1:W10")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub
I then modified it into a formula to use on multiple sheets
Sub SortRow(LastRow, LastCol, Sheet)
ActiveWorkbook.Worksheets(Sheet).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Sheet).Sort.SortFields.Add Key:=Range( _
Cells(1, 2), Cells(1, LastCol)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(Sheet).Sort
.SetRange Range(Cells(1, 2), Cells(LastRow, LastCol))
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub
Unfortunately the code does not work and simply sorts the first row and doesn't move any of the check boxes in the columns which obviously messes up my data. Any one have a fix?
heres an example of what I want
UNSORTED
---------SOP2 SOP1 SOP4 SOP3
mike-------x-----x-----x
dave-------x----------x--------
SORTED
-----------SOP1 SOP2 SOP3 SOP4
mike--------x----x----------x
dave-------------x----------x
I recorded a macro of me correctly sorting it manually
Sub Macro1()
'
' Macro1 Macro
'
'
Range("B1:W10").Select
ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Sort.SortFields.Add Key:=Range( _
"B1:W1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Sort
.SetRange Range("B1:W10")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub
I then modified it into a formula to use on multiple sheets
Sub SortRow(LastRow, LastCol, Sheet)
ActiveWorkbook.Worksheets(Sheet).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Sheet).Sort.SortFields.Add Key:=Range( _
Cells(1, 2), Cells(1, LastCol)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(Sheet).Sort
.SetRange Range(Cells(1, 2), Cells(LastRow, LastCol))
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub
Unfortunately the code does not work and simply sorts the first row and doesn't move any of the check boxes in the columns which obviously messes up my data. Any one have a fix?