Thread: Automatic Macro Sorting of cells

Hi there

I have a number of cells that need sorting at different areas of the spreadsheet but I want only one macro that sorts the cells depending on where the macro button is located. There will be many of the same button located at different rows in the spreadsheet, 365 to be precise as I need to sort something into order for each day of the year, but obviously I don't want 365 individual macros in my spreadsheet.

The code below is EXACTLY what I want the macro to do (the cell references are an example):

Range("B160:EQ209").Select
Selection.Sort Key1:=Range("C160"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

The macro button in this example is located in the B159 cell so therefore the two Ranges would need to be relative to that cell.

I think I would be right to include this in the macro, so that the macro knows where the button is located:

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

The Range("B160:EQ209").Select and the Range("C160") parts of the macro are the parts I want to be automated.

I tried using r.Row+1, r.Column+146, etc, but it didn't like this method.

Does anyone have the solution to this problem?

Thanks in advance,
Liam

Hi there

It seems I've been able to work out through trial and error the above issue. I apologise for posting without exploring further, however I have now run into another problem.

I will include the complete Macro below:

Sub SortStaffByShift()

ActiveSheet.Unprotect

Dim x As Range
Set x = ActiveCell

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

Dim a As Range
Set a = r.Offset(1, 0)

Dim b As Range
Set b = r.Offset(50, 145)

Dim c As Range
Set c = r.Offset(1, 1)

Range(a, b).Select
Selection.Sort Key1:=Range(c), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

ActiveSheet.Protect

x.Select

End Sub


Now, using Range(a, b).Select works fine with my initial problem of wanting to select a range of cells depending on the button location, but the macro crashes with a Run-time error 1004: Method 'Range' of object '_Global' failed error when it gets to the Blue part of the code above. Basically I want to sort the cells based on the value in the cell that is 1 row and 1 column to the right of the macro button, which will obviously be different each time the button is clicked from a different location on the spreadsheet.