Vba Doubble click → Copies table row, then paste the copied row by inserting it into another table in the same worksheet.

[BlacklyoN7M]

Hello everybody,

Before all thank you for your time!

I have 2 tables in the same worksheet.
-Table 1 (rows → G5:K) → yet I only want to copy the information from row H to Row K.
-Table 2 (rows → M5:P)

So the idea is to copy the information from the selected row in table 1 and to insert this information into table 2. This will allow me to easily transfer information that I will like to use and analyze for my business.

Underneath I have a basic code, which only copies and pastes:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Intersect(Range("H5:K500"), Target) Is Nothing Then Exit Sub
[M5] = Target.Value
End Sub

So the extra things that I will like to include in this code are:
1. Copies not only the selected cell but rows from H:K from table1
2. It inserts the information rather than paste it into the table2.


I try to be as clear as I could, so I hoop you understand everything,

Thank you in advance for reading and your help.

Have a great life!

 

[Yongle]

Welcome to MrExcel

Test this and report back

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("H5:K500"), Target) Is Nothing Then
        Cancel = True
        Range("M" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Range("H" & Target.Row).Resize(, 4).Value
    End If
End Sub

 

[BlacklyoN7M]

Hallo Yongle,

It worked perfectly! Yet there was a little problem.

When the table was empty, the information did not get included in the table when pasted. I managed to resolve this problem by not eliminating the first row of the table.

So yeah! It's working great and it helps me a lot with my work.

Thank you for your time!

But yeah for other people you could include another VBA code, that does manage to be included in the table when the table is empty.