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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

GlennUK

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

Forum statistics

Threads
1,144,282
Messages
5,723,474
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top