Advanced Filter

[elgringo56]

When I do an Advanced Filter on a list with a copy to another location, the formats(color, font, etc.) of the cells which were filtered are also copied along with the data. Is there a way to prevent this as I only want the data.

 

[Barry Katcher]

Elgringo, try the following code and see the sample below. The data range, criteria range and the upper left cell are predefined as named ranges. The filter uses "filter in place" and the paste special uses "formulas", which will copy and paste values and formulas, but not formatting.

Sub AdvFilterAndCopyPaste()
    Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("CriteriaRange"), Unique:=False
    Range("DataRange").Select
    Selection.Copy
    Range("TargetCell").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveSheet.ShowAllData
    Range("A1").Select
    Application.CutCopyMode = False
End Sub[RANGE=cls:xl2bb-100][XR][XH=cs:6]Book1[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]1[/XH][XD=h:c|bc:ffff00]Data1[/XD][XD=h:c|bc:ffff00]data2[/XD][XD=h:c|bc:ffff00]data3[/XD][XD=h:c|bc:ffff00]Total[/XD][XD=h:c][/XD][/XR][XR][XH]2[/XH][XD=h:c|bc:ffff00]1[/XD][XD=h:c|bc:ffff00]5[/XD][XD=h:c|bc:ffff00]9[/XD][XD=h:c|bc:ffff00|cls:fx][FORMULA==SUM(A2:C2)]15[/FORMULA][/XD][XD=h:c][/XD][/XR][XR][XH]3[/XH][XD=h:c|bc:ffff00]2[/XD][XD=h:c|bc:ffff00]6[/XD][XD=h:c|bc:ffff00]10[/XD][XD=h:c|bc:ffff00|cls:fx][FORMULA==SUM(A3:C3)]18[/FORMULA][/XD][XD=h:c][/XD][/XR][XR][XH]4[/XH][XD=h:c|bc:ffff00]3[/XD][XD=h:c|bc:ffff00]7[/XD][XD=h:c|bc:ffff00]11[/XD][XD=h:c|bc:ffff00|cls:fx][FORMULA==SUM(A4:C4)]21[/FORMULA][/XD][XD=h:c][/XD][/XR][XR][XH]5[/XH][XD=h:c|bc:ffff00]4[/XD][XD=h:c|bc:ffff00]8[/XD][XD=h:c|bc:ffff00]12[/XD][XD=h:c|bc:ffff00|cls:fx][FORMULA==SUM(A5:C5)]24[/FORMULA][/XD][XD=h:c][/XD][/XR][XR][XH]6[/XH][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]7[/XH][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]8[/XH][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c]Data1[/XD][/XR][XR][XH]9[/XH][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c]3[/XD][/XR][XR][XH]10[/XH][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]11[/XH][XD=h:c]Data1[/XD][XD=h:c]data2[/XD][XD=h:c]data3[/XD][XD=h:c]Total[/XD][XD=h:c][/XD][/XR][XR][XH]12[/XH][XD=h:c]3[/XD][XD=h:c]7[/XD][XD=h:c]11[/XD][XD=h:c|cls:fx][FORMULA==SUM(A12:C12)]21[/FORMULA][/XD][XD=h:c][/XD][/XR][XR][XH=cs:6][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]