I am trying to create 4 lists of unique values that I can then use for a
sorting function on my sheet. The code below runs on opening of the sheet.
Typically on opening the wb the first time or if I manually involk the
advancedfilter function it works fine. This code even works until there is a
change to the source ranges (ie adding/deleting a record). Once that happens
the advanced filter fails to copy the data to the new location. Either all
three instances work OK or none do. It seems to execute OK, with no error,
but actually copies nothing.

'Determine height of dataset
Range("RPdb").Select
intRAdbH = Selection.Rows.Count
'Clear current sort lists
Range("RPdblist").Select
Selection.ClearContents
Range("RAdblist").Select
Selection.ClearContents
Range("RAmgrlist").Select
Selection.ClearContents
Range("RAspclist").Select
Selection.ClearContents

'Create new lists
Range("RPdb").Select
ActiveCell.Offset(intRAdbH + 5000, 0).Select
Selection.Name = "RPdbprime"
Range("RPdb").Select
Selection.Copy
Range("RPdbprime").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("RPdbprime").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "RPdblist"
Selection.Sort Key1:=Range("RPdblist"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("RAdb").Select
ActiveCell.Offset(intRAdbH + 5000, 0).Select
Selection.Name = "RAdbprime"
Range("RAdbprime").Select
Range("RAdb").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"RAdbprime"), Unique:=True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "RAdblist"
Selection.Sort Key1:=Range("RAdblist"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("RAmgr").Select
ActiveCell.Offset(intRAdbH + 5000, 0).Select
Selection.Name = "RAmgrprime"
Range("RAmgr").Select
ActiveCell.Offset(intRAdbH + 1000, 0).Select
Selection.Name = "RAmgr1lbl"
Range("RAmgr").Select
Selection.Copy
Range("RAmgr1lbl").Select
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "RAmgr1"
Range("RAmgrprime").Select
Range("RAmgr1").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"RAmgrprime"), Unique:=True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "RAmgrlist"
Selection.Sort Key1:=Range("RAmgrlist"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("RAmgr1").Select
Selection.ClearContents

Range("RAspc").Select
ActiveCell.Offset(intRAdbH + 5000, 0).Select
Selection.Name = "RAspcprime"
Range("RAspc").Select
ActiveCell.Offset(intRAdbH + 1000, 0).Select
Selection.Name = "RAspc1lbl"
Range("RAspc").Select
Selection.Copy
Range("RAspc1lbl").Select
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "RAspc1"
Range("RAspcprime").Select
Range("RAspc1").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"RAspcprime"), Unique:=True
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "RAspclist"
Selection.Sort Key1:=Range("RAspclist"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("RAspc1").Select
Selection.ClearContents

Range("RAact").Select