Sorting columns containing checkboxes by values in header row

[TrinColll]

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

[VBA]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[/VBA]

I then modified it into a formula to use on multiple sheets

[VBA] 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[/VBA]

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?

[snb]

[VBA]
with ActiveWorkbook.Worksheets("SOP REQUIREMENTS")
.Range("B1:W10").Sort .cells(1,2)
end with
[/VBA]

[p45cal]

A little testing (without any checkboxes) shows this works all right when I have the sheet concerned as the active sheet (you have a few unqualified references). So attention shifts to the calling sub and whether LastRow and LastCol are assigned correct values, so we'd need to see that code too.

I could offer an alternative.
Instead of calling the sub with three arguments, call it with just one, being just the qualified range that you want to be sorted, like:
SortRow ActiveSheet.Range("B1:W10")
or:
SortRow ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Range("B1:W10")

With horizontal sorting, you'll have discovered while recording your macro that the my data has headers checkbox is greyed out but that the code still comes out with .Header = xlGuess. It seems only ever to sort the region you give it assuming that there are no headers in the first column, so I changed that to .Header = xlNo below to remove doubt.
So it's important when passing that range that it doesn't include the headers column.
Anyway:[vba]Sub test()
SortRow ActiveWorkbook.Worksheets("SOP REQUIREMENTS").Range("B1:W10")
End Sub

Sub SortRow(TheRange)
With TheRange.Parent.Sort
.SortFields.Clear
.SortFields.Add Key:=TheRange.Cells(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange TheRange
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub[/vba]