Help - Function not working

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

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

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Try this:
Code:
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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:
Code:
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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:
Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

Hi Mark,

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

Denis
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Just ran this sample in a dummy sheet with 650 rows of data, and it worked fine:

Code:
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
Just ran this sample in a dummy sheet with 650 rows of data, and it worked fine:

Code:
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? :eek:
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top