Help - Function not working

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,970
Office Version
  1. 2010
Platform
  1. Windows
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Hi Mark,

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

Denis
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,575
Messages
6,143,311
Members
450,477
Latest member
teresab543

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
Back
Top