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
Bookmarks