Enhancing a Macro to detect which Row it's triggered from

Hi all,
Hoping I could get some assistance with this - I usually only dabble in Macros, but this needs some VBA to enhance it.

Below is a very simple Macro that Copies part of Row 20 (B20:H20), and pastes it into Row 1 at B.

What I actually want to do is use Cell B in every Row on the sheet (including new 'Inserted' Rows) as a trigger to Copy that Row's range, say B:H, and ALWAYS paste it into the same target Row, say Row 1, at B.




Sub CopyPasteExample()
'
' CopyPasteExample Macro
'

'
Range("B20:H20").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
End Sub




Thanking you.

Hi Trevor,
The wider objective involves amending an Invoice's data - one Row per Invoice. The start of the process involves making a copy of an Invoice's data and placing that in a dedicated Row for actioning the amendment.
So I want a Button (trigger cell) at the LH of each Row, that will copy that Invoice's data to the dedicated 'processing row'.

Does that help to clarify?

Regards.

Thank you Trevor, that sounds like what I need.

Tiny bit of 'Scope Creep' if I may:
Could you add a Confirmation Dialog box that will pop up -
"Are you ready to copy this Invoice for Amendment processing?

YES to proceed NO to cancel procedure"

Also, could you tell me where/how to insert this code when I get it? I could search Google, but you'll be able to tell me quickly.
And, is there a way to 'deactivate' this code easily? Rather than just delete it, I'm guessing a line of code inserted at the top could Deactivate it, but is there a Menu type option to Deactivate such a module/function?


Regards.

That looks great in the example file Trevor, thank you!!

I'll try and port across to my worksheet.

Kind regards.

Trevor,
Bit of an unforeseen issue.
Because the code is checking anytime Col B is selected/clicked, the Code is also triggered if the whole Row is selected by, for example, clicking on the Row number in the vertical LH heading.
How best to get around this? Maybe checking that it's only a B column Cell that is clicked?

Cheers.

Trevor, I posted it in to the code per below, but got Error Msg - rInterest variable not defined.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, rInterest) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then Exit Sub

Const msg As String = "Are you ready to copy this Invoice for Amendment processing?"

Dim rInterest As Range
Set rInterest = Range("B2:B" & Rows.Count)

If Intersect(Target, rInterest) Is Nothing Then Exit Sub

Dim vResponse
vResponse = MsgBox(msg, vbYesNo, "Confirm")
If vResponse <> vbYes Then Exit Sub

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("B" & Target.Row).Resize(, 7).Copy Range("B1")

End Sub

Yes, thank you, I repositioned it before you posted, and got it working fine.
(Maybe it'd be nice if Excel had a 'ClickSelect' function that would become active if a Cell was Clicked on, rather than being Selected in any other way...)

Some 'Give Back' for the community:

1. This blog post by influential and respected Packy McCormick "Excel Never Dies" is IMO wonderful:
https://www.notboring.co/p/excel-never-dies

2. Given that maybe 90% of Excel work looks pretty awful, aesthetically-wise, it's so nice to see that with just the tools embedded already in Excel, you can create lovely-looking stuff.
And here's just one example I tripped over yesterday - (I've got no affiliations)
https://www.datastudiopro.com/