Set rng1 not working

[David A.]

I have:
Sub RepStats()
On Error Resume Next
Dim rng1 As range
Set myrng = Worksheets("Rep Page").range("A:A")
For Each c In myrng
If c <> "" Then
Sheets("Rep Page").Select
em = c.Value
Sheets("Sheet1").Select
Selection.AutoFilter Field:=2, Criteria1:=em
With ActiveSheet.AutoFilter.range
On Error Resume Next
Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng1 Is Nothing Then
MsgBox em + " **No Data Found For This Rep**"
Else
Set rng = ActiveSheet.AutoFilter.range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Rep Page").range("B2")
End If

ActiveSheet.ShowAllData
Sheets("Rep Page").Select
End If
Next
End Sub

The line:
On Error Resume Next
Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

Is not working. Can you help me. There is data that is filtered but it wont
copy, it just goes to:
If rng1 Is Nothing Then
MsgBox em + " **No Data Found For This Rep**"

 

[Bernie Deitrick]

David,

Worksheets doesn't have an offset property:

Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)

Try just this, since you use the With construction:

Set rng1 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)

HTH,
Bernie
MS Excel MVP

 

[David A.]

That worked. One more thing, How do I get it to paste in the cell next to the
"em" value?

 

[Bernie Deitrick]

David,

Try:

rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Rep Page").Range(C.Address).Offset(0, 1)

Although you are switching from rng1 to rng - not sure what exactly you want to do...

HTH,
Bernie
MS Excel MVP

 

[David A.]

THANK YOU!!!!!! Man I was killing myself trying to figure that out......