Copy Autofilter Items

[mzsuga]

I have a "Model" column in row 5 and column H. How do I autofilter that row and then copy the items in the autofilter list on column H so that it shows the unique values then copying that to another sheet in cell A1. But I want everything in A1, so if the items in the autofilter list is Nissan, Toyota, Honda, it will show them all in 1 line separated by a comma.

[lucas]

You can copy the visible cells after a filter using
[VBA]

SpecialCells(xlCellTypeVisible)
[/VBA]

You can copy the data to a different cell and then concatenate it.

If you provide a little more info or a sample file maybe someone can offer more assistance.

[mzsuga]

You can copy the visible cells after a filter using
[vba]

SpecialCells(xlCellTypeVisible)
[/vba]
You can copy the data to a different cell and then concatenate it.

If you provide a little more info or a sample file maybe someone can offer more assistance.

This is the code I have. It don't work. So on the Data sheet, the column headings are in row 5, so then first I do an autofilter, then I go to column H and then copy the items in the autofilter list. Then I paste it to the cell A2 on the Summary sheet but it don't work.

Private Sub automatic_event()
Dim ms As Worksheet
Dim ms1 As Worksheet
Set ms = Worksheets("Details")

With ms.Range.Rows("5:5")
.AutoFilter = True
ms.Range(Cells(5, h)).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 1).Copy

Set ms1 = Worksheets("Summary")
ms1.Cells(2, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

End Sub

[lucas]

what exactly doesn't work? Where does it fail and what happens?

[mzsuga]

what exactly doesn't work? Where does it fail and what happens?

I get a 1004 run time error, application define error, on this line :

ms.Range(Cells(5, h)).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 1).Copy

[mdmackillop]

Plese post a workbook with sample data.

[Zack Barresse]

Hello there,

First of all, apply some kind of filter to it, not just turn the autofilter on. Then, if there is no data showing, the special cells collection you're using will fail, so there must be data. If you use error handling you can trap for this error. Something like...

[vba]on error resume next
your code here
if err.number <> 0 then
'no data
else
'data is there
end if
on error goto 0[/vba]