Copying unique values from a list using VBA - help!

[Jed Shields]

Hi guys,

I've got a list that will grow over time (Dynamic Name Range = UserName). I'd like to be able to copy the unique names to a number of different columns and different sheets eg Sheet1 Column E, Sheet 2 Column E, Sheet 3 Column etc. I've also given these places dynamic named ranges e.g. Sheet1UniqueUserName. Sheet2UniqueUserName etc.

I've found a bit of code that copies the unique names to the last column on the sheet and it works well:


Sub UniqueList()

'Populate control with

'unique list.


Dim LastRowcell As Range, LastColCell As Range
Set LastRowcell = Cells.Find("*", , , , xlByRows, xlPrevious)
Set LastColCell = Cells.Find("*", , , , xlByColumns, xlPrevious)
If Not LastRowcell Is Nothing Then
Range(Cells(1, 1), Cells(LastRowcell.Row, 1)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Cells(1, LastColCell.Column), _
Unique:=True
End If

End Sub


How do I change the location of the unique list to the ranges that I've mentioned, on different sheets?

Cheers,
Jed.

 

[Jed Shields]

Slight bump ...