Hi,

I get an error message of:

1004 - No list was found. Select a single cell within your list, and
then click the command again.

When I call the following sub:

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("A:BM")

'extract a list of Sales Reps
ws1.Columns("F:F").Copy _
Destination:=Range("BP1")
ws1.Columns("BP:BP").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("BN1"), Unique:=True
r = Cells(Rows.Count, "BN").End(xlUp).Row

'set up Criteria Area
Range("BP1").Value = Range("F1").Value

For Each c In Range("BN2:BN" & r)
'add the rep name to the criteria area
ws1.Range("BP2").Value = c.Value
'add new sheet and run advanced filter
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("HRCN_For_Payroll").Range("B P1:BP2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
Next
ws1.Select
ws1.Columns("BN:BP").Delete
End Sub


When I "debug" it highlights the following snippet of code:
ws1.Columns("BP:BP").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("BN1"), Unique:=True

Can anyone help - if i dont call the sub and just run as macro it all
works fine but i want to include it in vba and when i just try and
call the macro it doesnt work - any ideas would be much appreciated!!

Many thanks

Rachel