possible to copy filtered data into one cell?

[Danny54]

I have filtered data

wb1.Sheets(1).Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy


and want to copy it to a single cell on the target worksheet

wb.Sheets(1).Range("W" & i).PasteSpecial Paste:=xlPasteValues


I thought this would do it, but the cells are copied to as many cells as selected on the target when completed.


Its it possible to copy all the filtered data to one cell?

Thanks

 

[Joe4]

I don't think you can copy multiple cells into one single cell.
You would need to do something like loop through the multiple cells that you want to copy form, and write the results to a single VBA variable, then populate the single destination cell with the value of that variable.

 

[DanteAmor]

Hi @Danny54, If this is what you need, I hope the following example helps you:

Sub copy_into_one_cell()
  Dim wb1 As Workbook, wb As Workbook, c As Range, s As String, sep As Variant
  Set wb1 = ThisWorkbook
  Set wb = Workbooks("book2.xlsx")
  sep = "," [COLOR=#008000]'could be "|" or " " or chr(10) or vbtab[/COLOR]
  For Each c In wb1.Sheets(1).Range("F2", wb1.Sheets(1).Range("F" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
    s = s & c & sep
  Next
  If s <> "" Then s = Left(s, Len(s) - 1)
  wb.Sheets(1).Range("W" & 1).Value = s
End Sub

 

[Joe4]

Dante's code does what I was talking about, looping through the range, writing the matching results to a variable, and populating your destination cell with that variable.

If that does not give you what you want, please explain exactly what is in these cells, and what you want the final result to look like.

 

[Danny54]

Thanks so much!

Worked like a pro.

You guys have a "Wonderful Day"

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.