PDA

View Full Version : Copy Autofilter Items



mzsuga
09-17-2009, 08:37 AM
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
09-17-2009, 09:43 PM
You can copy the visible cells after a filter using


SpecialCells(xlCellTypeVisible)


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
09-18-2009, 09:14 AM
You can copy the visible cells after a filter using


SpecialCells(xlCellTypeVisible)

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
09-18-2009, 11:07 AM
what exactly doesn't work? Where does it fail and what happens?

mzsuga
09-18-2009, 11:44 AM
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
09-19-2009, 03:40 AM
Plese post a workbook with sample data.

Zack Barresse
09-19-2009, 10:35 AM
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...

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