Set rng1 not working
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." wrote in message
...
That worked. One more thing, How do I get it to paste in the cell next to the
"em" value?
"Bernie Deitrick" wrote:
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." wrote in message
...
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**"
|