Copy/Paste Values of Results of FILTER function

[10sGP]

First post on here.

I'm looking to double click one cell from a FILTER function results and get that row copied to another workbook.
For example, let's say that

[B]=FILTER(A5:D20,C5:C20=H2,"")[/B]

gives me 10 results. I want to double click Cell A of one of those 10 results and get the whole row copied to another workbook.
While searching, I came across the code shown below by Tom Urtis. With very little modification, it does what I need, but not for FILTER function results. I just learned you cannot just copy/paste FILTER function results, you have to copy/PASTE VALUES. I tried this:

.PasteSpecial Paste:=xlPasteValues

but that gives me an error.
Any ideas on how to accomplish this?

Thank you.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Cancel = True
With Sheets("Sheet3")
Target.EntireRow.Copy .Range("A" & .Cells(Rows.Count, 1).End(xlUp).row + 1)
End With
End Sub

 

[kevin9999]

This may or may not be an acceptable workaround for your problem. I don't know what your data looks like, or whether you really want to copy the entire row (or just columns A-D) so I've assumed for the purpose of this demonstration that you just want to copy the first 4 columns.

With a data set that starts like this:

Book1
	A	B	C	D	E	F	G	H
1
2								123
3
4	HDR1	HDR2	HDR3	HDR4
5	Row 5 Col 1	Row 5 Col 2	123	Row 5 Col 4
6	Row 6 Col 1	Row 6 Col 2	123	Row 6 Col 4
7	Row 7 Col 1	Row 7 Col 2	x	Row 7 Col 4
8	Row 8 Col 1	Row 8 Col 2	x	Row 8 Col 4
9	Row 9 Col 1	Row 9 Col 2	123	Row 9 Col 4
10	Row 10 Col 1	Row 10 Col 2	123	Row 10 Col 4
11	Row 11 Col 1	Row 11 Col 2	123	Row 11 Col 4
12	Row 12 Col 1	Row 12 Col 2	123	Row 12 Col 4
13	Row 13 Col 1	Row 13 Col 2	123	Row 13 Col 4
14	Row 14 Col 1	Row 14 Col 2	x	Row 14 Col 4
15	Row 15 Col 1	Row 15 Col 2	x	Row 15 Col 4
16	Row 16 Col 1	Row 16 Col 2	x	Row 16 Col 4
17	Row 17 Col 1	Row 17 Col 2	x	Row 17 Col 4
18	Row 18 Col 1	Row 18 Col 2	123	Row 18 Col 4
19	Row 19 Col 1	Row 19 Col 2	123	Row 19 Col 4
20	Row 20 Col 1	Row 20 Col 2	123	Row 20 Col 4
21
22
23
24
25	Row 5 Col 1	Row 5 Col 2	123	Row 5 Col 4
26	Row 6 Col 1	Row 6 Col 2	123	Row 6 Col 4
27	Row 9 Col 1	Row 9 Col 2	123	Row 9 Col 4
28	Row 10 Col 1	Row 10 Col 2	123	Row 10 Col 4
29	Row 11 Col 1	Row 11 Col 2	123	Row 11 Col 4
30	Row 12 Col 1	Row 12 Col 2	123	Row 12 Col 4
31	Row 13 Col 1	Row 13 Col 2	123	Row 13 Col 4
32	Row 18 Col 1	Row 18 Col 2	123	Row 18 Col 4
33	Row 19 Col 1	Row 19 Col 2	123	Row 19 Col 4
34	Row 20 Col 1	Row 20 Col 2	123	Row 20 Col 4
35
Sheet1

Cell Formulas
Range	Formula
A5:B20,D5:D20	A5	="Row "&ROW()&" Col " &COLUMN()
A25:D34	A25	=FILTER(A5:D20,C5:C20=H2,"")
Dynamic array formulas.

When you use the following double-click code (clicking just on column A of sheet 1)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 Then
        Cancel = True
        Application.EnableEvents = False
        Dim GoToCell As Range, a() As Variant, i As Long, c As Range
            ReDim a(1 To 4) As Variant
            i = 1
            For Each c In Range(Target.Address, Target.Offset(, 3))
               a(i) = c.Value
               i = i + 1
            Next c
            Set GoToCell = Sheet3.Range("A" & Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1)
            GoToCell.Resize(1, 4).Value = a
        Application.EnableEvents = True
    End If
End Sub

You get the following result on sheet 3, when you double-click on column A of rows 1, 3 and 10 of the array returned by the Filter() function:

Book1
	A	B	C	D
1	HDR1	HDR2	HDR3	HDR4
2	Row 5 Col 1	Row 5 Col 2	123	Row 5 Col 4
3	Row 9 Col 1	Row 9 Col 2	123	Row 9 Col 4
4	Row 20 Col 1	Row 20 Col 2	123	Row 20 Col 4
5
Sheet3

Is this close to what you're looking for?

 

[Peter_SSs]

See if this does what you want.
I have assumed that the FILTER formula you gave above is in cell A24, hence the code checking that the double-click is at least that far down the worksheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  With Target
    If .Column = 1 And .Row >= 24 Then
      Cancel = True
      .EntireRow.Copy
      Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
      Application.CutCopyMode = False
    End If
  End With
End Sub

 

[10sGP]

@kevin9999 and @Peter_SSs Thank you for your answers but I haven't been able to test them because when I double click a cell in the "A" column it just goes into the cell. Nothing happens.
I'm pretty new to VBA so I'm not sure what I'm doing wrong. Yesterday, the code I included in my post worked (without doing the pasting part I need) and today none of the codes I have tried work.
Not sure what's different. For example in the image below I double clicked the "A" cell in the second result.

 

[Peter_SSs]

We can't tell anything much from a picture. However, the first thing that I would try would be to close the workbook and also close right out of Excel and then start Excel again as your 'events' may have become disabled.

 

[10sGP]

@kevin9999 and @Peter_SSs,

Thank you so much for your help. Both your codes do what I need.
Now, as a first timer posting, I'm not sure what's the procedure in this case as far as "Mark as a solution" goes.
Can I mark both as a solution or do I need to pick one?

Thank you

 

[kevin9999]

Can I mark both as a solution or do I need to pick one?

You can only mark one solution. In the case where 2 or more solutions achieve your requirements equally well, you should pick the most efficient, which is (usually) the routine with the fewer lines of code. In this case, that would be Peter's (which is the one I'd pick)

 

[Peter_SSs]

Thank you so much for your help. Both your codes do what I need.

You're welcome. Glad we could help. Thanks for the follow-up.

Can I mark both as a solution or do I need to pick one?

As Kevin has indicated, you can only mark one. If one seems more efficient to you then you likely used that one and would mark it. If efficiency seems equal then you might use the one that made most sense to you or the one that you felt you might be best able to alter if required later & then you might mark that one. In the end, it doesn't matter much as it is really just an indication to future readers that a solution was found in the thread and they can look through and see if any of the posts suit them.