Help - Function not working

[MarkAndrews]

I have this code in my workbook - However, it is not copying the list to the required location - I am clueless as to why

TIA

    Sheets("Paste Completions Report Here").Select
    Columns("F:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
        "F:F"), CopyToRange:=Range("J1"), Unique:=True

 

[GlennUK]

What is happening? Does the macro run, but doesn't do what you want? Or is it that the macro crashes? Do you have any filtering on the sheet already?

 

[Barry Katcher]

Try this:

Sub Macro1()
    Sheets("Paste Completions Report Here").Select
    Columns("F:F").Select
    Range("F1:F12").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "J1"), Unique:=True
End Sub

I think your code was selecting F:F twice.

 

[MarkAndrews]

What is happening? Does the macro run, but doesn't do what you want? Or is it that the macro crashes? Do you have any filtering on the sheet already?

It's not copying the list to the required location?

There is no filter in place on the sheets before running this

Barry - Yours neither, sorry

 

[SydneyGeek]

Mark, if you have used Advanced Filter to grab a chunk of a table and paste into J1, then repeated it, it often seems to play up the second time around.

Drove me nuts until I added a line to delete the columns containing the filtered data before repeating the extraction.

In your case:

Sub Macro1()
    Sheets("Paste Completions Report Here").Select
    Columns("J:J").Delete
    Range("F1:F12").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "J1"), Unique:=True
End Sub

Denis

 

[MarkAndrews]

Mark, if you have used Advanced Filter to grab a chunk of a table and paste into J1, then repeated it, it often seems to play up the second time around.

Drove me nuts until I added a line to delete the columns containing the filtered data before repeating the extraction.

In your case:

Sub Macro1()
    Sheets("Paste Completions Report Here").Select
    Columns("J:J").Delete
    Range("F1:F12").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "J1"), Unique:=True
End Sub

Denis

Thanks Denis - But this still isn't working

The location 'J' remains blank each time (there are 650 rows of data)

 

[SydneyGeek]

Hi Mark,

If there are 650 rows of data why are you only filtering the first 12 rows?

Denis

 

[SydneyGeek]

Just ran this sample in a dummy sheet with 650 rows of data, and it worked fine:

Sub Macro1()
    Dim Rw As Long
    Sheets("Sheet1").Select
    Rw = Range("F65536").End(xlUp).Row
    Columns("J:J").Delete
    Range("F1:F" & Rw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "J1"), Unique:=True
End Sub

Denis

 

[MarkAndrews]

Just ran this sample in a dummy sheet with 650 rows of data, and it worked fine:

Sub Macro1()
    Dim Rw As Long
    Sheets("Sheet1").Select
    Rw = Range("F65536").End(xlUp).Row
    Columns("J:J").Delete
    Range("F1:F" & Rw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "J1"), Unique:=True
End Sub

Denis

Still not working, sorry Denis?

 

[SydneyGeek]

Did you change the sheet reference from "Sheet1" to "Paste Completions Report Here"?

I'm assuming the table doesn't include column J and that you are trying to extract a unique list. Is that correct? Also, the stuff that you are extracting -- is it text / constants, or formulas?

Denis