MACRO Buttons moving when sorting

[Northern NY Design]

I have created macro buttons in every cell in column A but when I sort the spreadsheet, it moves the macros for the rows as well. How can I lock the macro buttons to the specific row; so they do not move with the data in the row when it is re-sorted?

 

[Logit]

I am unable to reproduce your problem here. Is there anything else in your workbook aside from the buttons and sortable data in Col A ?

 

[HighAndWilder]

I have created macro buttons in every cell in column A but when I sort the spreadsheet, it moves the macros for the rows as well. How can I lock the macro buttons to the specific row; so they do not move with the data in the row when it is re-sorted?

I have created macro buttons in every cell in column A but when I sort the spreadsheet, it moves the macros for the rows as well. How can I lock the macro buttons to the specific row; so they do not move with the data in the row when it is re-sorted?

I've managed to reproduce the problem and the buttons stay with their respective rows. What do the macros do because there may be a better way of achieving the objective?

 

[Joe4]

Yeah, I was thinking myself having a macro button on every row may be overkill.
If it is something like copying the row over to another place, it might be better just to have one button, and then have it either run against the "active" row when the button is clicked, or prompt the user to enter the row number.

 

[HighAndWilder]

Yeah, I was thinking myself having a macro button on every row may be overkill.
If it is something like copying the row over to another place, it might be better just to have one button, and then have it either run against the "active" row when the button is clicked, or prompt the user to enter the row number.

For that one task I would freeze row 1 and have a button there that copies the row of the active cell.

Maybe scroll to that row so that it appears below the button and than confirm with the user that that
is the correct row.

 

[Joe4]

Yep. I was thinking something along those same lines too.

 

[Northern NY Design]

LOL, lots of solutions. So, each row has a COPY and a DELETE button. This is so my managers can copy the specified data (row) from ACTIVE to INACTIVE rosters. The click the @ Button (COPY) and go to the INACTIVE tab and click the PASTE Button for that row, then go back to the ACTIVE tab and click the X Button (DELETE). After that, they have another button that allows them to reorder the roster in alpabetic order.

This is where the problem comes in; when they resort the roster, the MACRO button moves with the row... so for instance, what was ROW 1, after sorting is ROW 3 but the BUTTON is still linked to ROW 1.

 

[Joe4]

Having buttons for every row can be very cumbersome, as if you add more lines you will need to add more buttons.
Have you give thought to the suggestions we gave you? Only have one button, up in row one, which you can use Freeze Panes to always make sure it is visible.
Then you can either have your code run against the active/selected row at the time the button is pushed, or prompt the user to enter the row to run against.

That seems to be a much more efficient and easy to manage solution that having buttons on every row (which I would never do in a million years - you are just making more work and problems for yourself!).

 

[HighAndWilder]

LOL, lots of solutions. So, each row has a COPY and a DELETE button. This is so my managers can copy the specified data (row) from ACTIVE to INACTIVE rosters. The click the @ Button (COPY) and go to the INACTIVE tab and click the PASTE Button for that row, then go back to the ACTIVE tab and click the X Button (DELETE). After that, they have another button that allows them to reorder the roster in alpabetic order.

This is where the problem comes in; when they resort the roster, the MACRO button moves with the row... so for instance, what was ROW 1, after sorting is ROW 3 but the BUTTON is still linked to ROW 1.

We are assuming that row 1 contains the column headings which is normally the case.

Is having column headings out of the question?

The code behind the button could copy and paste and delete the row and also sort the roster in one go.

Alternatively, have a column in the ACTIVE sheet called Status with a Validation List of either blank or Inactive.

When the Status changes to 'Inactive' then the row is copied / deleted and then the INACTIVE worksheet is reordered.

Another option would be to only copy the 'Inactive' rows to the INACTIVE worksheet when that worksheet is activated.

 

[Northern NY Design]

We are assuming that row 1 contains the column headings which is normally the case.

Is having column headings out of the question?

The code behind the button could copy and paste and delete the row and also sort the roster in one go.

Alternatively, have a column in the ACTIVE sheet called Status with a Validation List of either blank or Inactive.

When the Status changes to 'Inactive' then the row is copied / deleted and then the INACTIVE worksheet is reordered.

Another option would be to only copy the 'Inactive' rows to the INACTIVE worksheet when that worksheet is activated.

Sorry; I was sick last week and did not look at this. See the image of the spreadsheet below as well as the code for the MACRO.

Sub CLEARROW1()
'
    If MsgBox("This is your friendly Lon-bot, letting you know that if you click YES, this driver will be PERMANENTLY removed from the CURRENT DRIVER DATA tab. ARE YOU SURE YOU WANT TO DO THIS, THIS CANNOT BE UNDONE!", vbYesNo) = vbYes Then

' CLEARROW1 Macro
' Use this MACRO to remove a driver from ROW 1 of the CURRENT DRIVER DATA without removing formulas or harming this spreadsheet set.
'
    Sheets("CURRENT DRIVER DATA").Select
    ActiveSheet.Unprotect
    Range("A3:C3").Select
    Selection.ClearContents
    Range("E3").Select
    Selection.ClearContents
    Range("G3:AC3").Select
    Selection.ClearContents
    Range("AE3:AF3").Select
    Selection.ClearContents
    Range("AG3:AN3").Select
    Selection.ClearContents
    Range("AS3:AZ3").Select
    Selection.ClearContents
    Range("BE3:BL3").Select
    Selection.ClearContents
    Range("BQ3:BX3").Select
    Selection.ClearContents
    Range("CC3:CJ3").Select
    Selection.ClearContents
    Range("CO3:CV3").Select
    Selection.ClearContents
    Range("DA3:DH3").Select
    Selection.ClearContents
    Range("DM3:DT3").Select
    Selection.ClearContents
    Range("DY3:EF3").Select
    Selection.ClearContents
    Range("EK3:ER3").Select
    Selection.ClearContents
    Range("EW3:FD3").Select
    Selection.ClearContents
    Range("FI3:FP3").Select
    Selection.ClearContents
    Range("C3").Select
    Sheets("CURRENT DRIVER DATA").Select
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
End Sub