Error on advanced filter vba excel - 1004

[Rachel Curran]

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("BP1: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

 

[Dave Peterson]

It scares me that you have a lot of unqualified range objects in your code.

Range("a:bm")
refers to the activesheet.

I'm not sure that's always what you want.

I qualified the ranges (using with and dots):

Option Explicit
Sub ExtractReps()

Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Long
Dim c As Range

'extract a list of Sales Reps
Set ws1 = Sheets("Sheet1")
With ws1
Set rng = .Range("A:BM")
.Columns("F:F").Copy _
Destination:=.Range("BP1")

.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
.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("BP1:BP2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
Next
.Columns("BN:BP").Delete
End With

End Sub

But I'm not sure it refers the worksheets that you wanted.

With ws1
set rng = .range("a:bm")
.....

This dot-range means that it belongs to the last With object--in this case, ws1.