Copy Autofilter Items 1

[michaela18]

I have a "Model" column in row 5 and column H. How do I autofilter that row 5 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. Also in order to automatically execute this macro, I would have to put this in the sheet in the editor by using the private sub function?

 

[Gavona]

I would sort by H then use 2 helper columns.
The first would have a formula that resulted in a value of "ignore" except for those rows where the value in column H was different to the row above where the value would be equal to that in column H.

The second helper column would contain a formula that concatenated the value from column A with the value from the row below unless the row below did not contain "ignore" in the next row.

next step would be to filter the first helper column for <>"ignore" and copy visible cells to your new workbook.

To automate you would need to use events. Alternately you could attach the macro to a button.

Gavin

 

[Gavona]

Maybe the explanation is hard to follow?
In J2:
=if(H2<>H1,H2,"ignore")
in K2:
=if(H3<>"ignore",K3&","&H2,H2)



Gavin

 

[michaela18]

Thanks for replying back. I appreciate it. What i really wanted was a macro because the one I have now, I'm stuck on. The problem with having formulas is that this report is automaticlaly generated and it will change in the amount of data in it. So I wanted to have a macro on the template itself so when it generates the report, it will automatically have those.

 

[Gavona]

Of course. I would record myself doing the above then edit the macro. Use features such as Filldown or Shift-Ctrl-Down to extend range.
Post your recorded macro and we can help to 'generalise it'.

Gavin

 

[michaela18]

here is what I have

Private Sub auto()

Dim ms As Worksheet
Dim ms1 As Worksheet
Set ms = Worksheets("Data")

With ms.Range.Rows("5:5")
.AutoFilterMode = True
End With
With ms.Range(Cells(5, h))
.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 1).Copy
Set ms1 = Worksheets("Summary Sheet")
ms1.Cells(2, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

End Sub

 

[Gavona]

Apologies the second formula should be =IF(J3="ignore",K3&","&A2,A2)

Does every row in your data set have some values or do you have blank rows in the middle?
Does every column have a field heading?
In other words if you select cell H2 then Ctrl-A do you select the entire data table?

Gavin

 

[Gavona]

Ok, here is the macro I recorded for the formulae, followed by my simplification of it.

Sub Macro1()
    Range("H1").Select
    Selection.End(xlDown).Select
    Range("J6").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("J2:J6").Select
    Selection.FormulaR1C1 = "=IF(RC[-2]<>R[-1]C[-2],RC[-2],""ignore"")"
    Range("K2:K6").Select
    Selection.FormulaR1C1 = "=IF(R[1]C[-1]=""ignore"",R[1]C&"",""&RC[-10],RC[-10])"
End Sub

Sub Macro2()
    With Range(Range("H2"), Range("H2").End(xlDown))
        .Offset(0, 2).FormulaR1C1 = "=IF(RC[-2]<>R[-1]C[-2],RC[-2],""ignore"")"
        .Offset(0, 2).Value = .Offset(0, 2).Value 'copies to value
        .Offset(0, 3).FormulaR1C1 = "=IF(R[1]C[-1]=""ignore"",R[1]C&"",""&RC[-10],RC[-10])"
    End With
End Sub

I don't know what [red]SpecialCells(xlCellTypeConstants, 1)[/red] does in your code

Gavin

 

[michaela18]

Okay I see it now. Thank you for everything!