Macro Help: Advanced Filter Unique Values to New Column

[ACF0303]

Greetings All,

I am trying to create a macro that will take a list of names and using the advanced filter, copy the unique values to another location. The new list of unique names is being used to populate another area of the workbook. Seems easy, but for some reason, it will not work twice. When I go to the original list and make a change (say change a name from "General Hospital" to "Anytown Hospital"), the macro seems to run, but it does not make the change. I added in some code to clear the destination contents for the unique list, but it's not working. Any suggestions would be very welcome!


Range("V1:V46").Select
Selection.ClearContents
Range("S1:S46").Select
Range("S1:S46").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"V1:V46"), Unique:=True
Sheets("Client Info").Select
Range("F5").Select
End Sub

Thanks!
Cathy

 

[ukmikeb]

Hi

Try :-

Range("S1:S46").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("V1"), Unique:=True

Also, you don't need that Range(..).Select prior to that statement and the previous 2 lines can be merged into :-

Range("V1:V46").ClearContents

hth

 

[Jerry Sullivan]

Hi all,

Mike's code is cleaner (removing the unnecessary Selections and by specifying V1 as the target); however I don't see how those changes would remedy whatever problem caused the results Cathy described in the OP. It's hard to guess what that might be since the entire Sub is not shown and we don't know what sheet the user is on when the macro is run.

Cathy, I'd suggest you use the F8 key from the VB Editor to step through your code one line at a time to see if the problem becomes evident.
Since the range references shown in the code snippet are not qualified with Worksheet references then one possible cause is that the ActiveSheet that's being assumed isn't consistent between runs that work and runs that don't.

 

[ACF0303]

Thank you both! And you are both right. I am a novice (so using the "record macro" function which leaves things messy) and the macro was missing a very important part....which page to start from. I'm embarrassed for wasting your time!

Cathy

 

[Fazza]

A little late. For your info, some handy-for-me code I have in my personal macros - on a shortcut CTRL-U (for uniques).

Highlight header and data, run macro. Puts unique list on RHS of existing data.

Note can run on multiple columns. I use this most days.

Sub UniqueItems()
  
  On Error GoTo ErrorHandler
  With Selection
    .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 2), Unique:=True
  End With
  
ResumeHere:
  Exit Sub
  
ErrorHandler:
  MsgBox prompt:="Something went wrong !!", Buttons:=vbCritical, Title:="Error ..."
  GoTo ResumeHere
End Sub