How to copy a row of values from one sheet to another in one cell click?

Question

I have a follow up question for this previous question I had. I did not include this question there to avoid confusion.

So I already have a complete working Submit function that saves everything from Page1 (Sheet1) to Page2 (Sheet2).

Now, whenever I will click a random ticket number in Page2, I would like to have it copied to Page3 (Sheet3) with its details autopopulated - Date, Time, Ticket, Score and Yes/No/NA answers.

Page2

Using the image of Page2 above, if any cell with ticket number in Range C is clicked, it will copy its whole row of values to Page3.

For example when I click on Ticket 7789 2024,

Values of Date, Time, Ticket will go to cells E3:E5 of Page 3
Value of Score will go to J3 of Page3
Values of Yes/No/NA answers from Range E:BC of Page2 will go to cells E7:57 of Page3

It will show like this when I click the Ticket number 7789 2024 of image Page2 above:

Page3

What I have started is this code put in the Page2 sheet module (I'd be honest, I don't know how to continue it):

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    
    Dim sh, sh2 As Worksheet
    Set sh = ThisWorkbook.Sheets("Page2")
    Set sh2 = ThisWorkbook.Sheets("Page3")
    
    
    If Selection.Count = 1 Then
        If Not Intersect(Target, sh.Range("C:C")) Is Nothing Then
            'Values of Date, Time, Ticket from Page2 will go to cells E3:E5 of Page 3
            'Value of Score from Page2 will go to J3 of Page3
            'Values of Yes/No/NA answers from Range E:BC of Page2 will go to cells E7:57 of Page3
        End If
    End If
End Sub

Your help is greatly appreciated.

Answer 1

• Add Worksheet_SelectionChange() to Page2 module in VBE
• Me is a reference to Page2 sheet object

Microsoft documentation:

Range.CountLarge property (Excel)

Range.Offset property (Excel)

Range.Resize property (Excel)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .CountLarge = 1 Then
            If .Row > 2 And .Column = 3 And Len(.Value) > 0 Then
                Application.EnableEvents = False
                Dim oSht As Worksheet
                Set oSht = Sheets("Page3") ' modify as needed
                oSht.Range("E3").Resize(3, 1).Value = Application.Transpose(.Offset(, -2).Resize(1, 3).Value)
                ' you may need to apply date and time formatting
'                oSht.Range("E3").NumberFormatLocal = "m/d/yyyy;@"
'                oSht.Range("E4").NumberFormatLocal = "[$-en-US]h:mm:ss AM/PM;@"
                oSht.Range("J3").Value = .Offset(, 1).Value
                oSht.Range("E7").Resize(51, 1).Value = Application.Transpose(.Offset(, 2).Resize(1, 51).Value)
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub