0

I want to be able to use a hyperlink in an excel spreadsheet to be able to cut a row (or a selection of a row) and paste it as the next blank row of another sheet. This would basically "move" the record from one sheet to another or others.

For example, below I'd like to click "to sheet 2" hyperlink and the row clicked would be selected, cut and pasted into sheet 2. Or sheet 3 when that hyper link was clicked.

enter image description here

I haven't found a way to do this easily. Any suggestions?

jroyce
  • 2,029
  • 2
  • 22
  • 45
  • 1
    See here: http://stackoverflow.com/questions/28728600/in-excel-can-i-use-a-hyperlink-to-run-vba-macro – OpiesDad Sep 08 '16 at 22:12
  • That was helpful and I saw that as part of my research. What that doesn't do is keep a relative reference to the row and cell and it is hard coded into the hyperlink. If I need 50 or 100 rows I don't want to create hyperlinks for each row added. Is that possible? – jroyce Sep 08 '16 at 22:58
  • 1
    The "Worksheet_FollowHyperlink" method will be called any time any hyperlink in the sheet is clicked on. The hyperlink is then the "Target" variable which is a parameter. To see this, insert the code `MsgBox "row" & Target.Range.Row & " column " & Target.Range.Column` instead of the message box that is in the code in that question. I'm not sure what you mean that it doesn't keep a relative reference. – OpiesDad Sep 08 '16 at 23:11
  • 1
    You need to put the subroutine in the module for the Sheet you want it in, not in "ThisWorkbook" or in a different module. – OpiesDad Sep 08 '16 at 23:12
  • Your answer is exactly what I needed and solved the problem. Make this an answer as it was not clear in any other question before. The key was "Target.Range.Row" and not the "ActiveCell.Row". Perfect! – jroyce Sep 08 '16 at 23:15

1 Answers1

1

Per In Excel, can I use a hyperlink to run vba macro? it shows how to capture the event of pressing a hyperlink.

Put the code in the module for the Sheet you want it to work on:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        MsgBox "row" & Target.Range.Row & " column " & Target.Range.Column

End Sub

This obviously just shows a pop-up of the cell that the hyperlink is in, but you can do whatever you want once you get into this event procedure.

Community
  • 1
  • 1
OpiesDad
  • 3,385
  • 2
  • 16
  • 31
  • Super simple and helpful. Thanks for the fast reply. – jroyce Sep 08 '16 at 23:28
  • Also, in case you didn't notice, note that you can capture the name/caption of the hyperlink in the `target` object so you can use that to determine which sheet to move the row to. – OpiesDad Sep 09 '16 at 18:19