Private sub that copies variable row on double click and fills in form on different worksheet

Question

I'm stuck getting the copied row from sheet 1, where the private sub is, into the form on sheet 2 ("Rechnung"). Right now the private sub copies a whole variable row to the "Rechnung"-sheet and from there it should be filled into a billing form. I recorded the filling process with a makro which I now can't combine with the private sub.

I also tried Worksheet_Change(ByVal Target As Range) on the "Rechnung" sheet, but it filled the form before the first code could paste the contents and blocked the copying...

Here's where I'm at:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 106
    'copy content of clicked row to "Rechnung"
    
    Dim Trigger     As Range
    Dim R           As Long
    
    ' start from H2 until the end of column H
    Set Trigger = Range(Cells(2, "H"), Cells(Rows.Count, "H").End(xlUp))
    If Not Application.Intersect(Target, Trigger) Is Nothing Then
        R = Target.Row
        Range(Cells(R, 1), Cells(R, Columns.Count).End(xlToLeft)).Copy _
              Destination:=Worksheets("Rechnung").Range("I17")
        Cancel = True               ' exit Edit mode (usual function of Double-click)
    End If
End Sub

Until here everything is working great and the intended row is being copied to sheet 2 ("Rechnung"), I17.

1. table with gigs
2. copying cells from sheet1 to sheet2

From here on I'm stuck. The cells I17:S17 on sheet 2 are after being filled in by the previous code supposed to be copied to the final cells of the form.

For example K17 has to be copied to G21 J17 has to be copied to G25 (with the special option PasteValuesAndNumberFormats) etc.

Like that:
3. filling in invoice form with cells of copied row

At last it'd be great to delete the copied line and set the background color to white:
4. invoice clean

How can I do that best? Thank you already for help!

All the best, Thomas

Answer 1

I recommend this code to you. Please consider it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 106
    'copy content of clicked row to "Rechnung"
    
    Dim Trigger     As Range
    Dim Arr         As Variant              ' array of source data
    Dim Src         As Variant              ' designated column numbers of Arr
    Dim Tgt()       As String               ' cell addresses in "Rechnung" to match Src
    Dim R           As Long
    
    ' start from H2 until the end of column H
    Set Trigger = Range(Cells(2, "H"), Cells(Rows.Count, "H").End(xlUp))
    If Not Application.Intersect(Target, Trigger) Is Nothing Then
        R = Target.Row
        Arr = Range(Cells(R, 1), Cells(R, Columns.Count).End(xlToLeft)).Value
        
        ' by their column, specify the cells from Arr you want to carry to "Rechnung"
        Src = Array(1, 4, 7)
        ' specify the cells in "Rechnung" where you want them to appear
        '   Tgt may not have fewer elements than Src
        Tgt = Split("I17,C4,D28", ",")
        
        For R = 0 To UBound(Src)
            Worksheets("Rechnung").Range(Tgt(R)).Value = Arr(1, Src(R))
        Next R
        Cancel = True               ' exit Edit mode (usual function of Double-click)
    End If
End Sub

The idea is that you double-click on a cell in column H of your Sheet1 and the data of the double-clicked row are copied to the Rechnung tab.

I have expanded my answer to include a system for transferring the data. It has three parts as follows. Arr which is a copy of all values found in the clicked row (faster than picking them one by one), Src, a list of values you want to transfer, identified by their column number in Arr, and Tgt, a corresponding list of cell addresses in "Rechnung" where you want the data to appear.

Make sure that the columns you specify in Src actually exist in Arr and that the number of columns specified in Src do have a matching number of recipient cells in Tgt.