Hi All.......

I have a macro obtained long ago from this group......I keep re-using it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it worked when
I first got it, (maybe not, I almost never have no results on the autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

Hi CLR

See this example that use
If rng2 Is Nothing Then

http://www.contextures.com/xlautofilter03.html#Copy


--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message ...
Hi All.......

I have a macro obtained long ago from this group......I keep re-using it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it worked when
I first got it, (maybe not, I almost never have no results on the autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

Thanks for the response Ron, but that is the exact code I'm using......just
modified for my circumstance.

Vaya con Dios,
Chuck, CABGx3



"Ron de Bruin" wrote:

Hi CLR

See this example that use
If rng2 Is Nothing Then

http://www.contextures.com/xlautofilter03.html#Copy


--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message ...
Hi All.......

I have a macro obtained long ago from this group......I keep re-using it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it worked when
I first got it, (maybe not, I almost never have no results on the autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

All seems well now.....dunno what changed......musta been cockpit
trouble......sorry Guys.......

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Hi All.......

I have a macro obtained long ago from this group......I keep re-using it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it worked when
I first got it, (maybe not, I almost never have no results on the autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

Sounds like you don't have a filter on at all Chuck. You could test that

Sub CopyFilterInquiry()
Dim rng As Range
Dim rng2 As Range

Sheets("DATAINPUT").Select
If Not ActiveSheet.AutoFilterMode Then
MsgBox "No autofilter set"
Else
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select
End If
End Sub

--
HTH

Bob Phillips

"CLR" wrote in message
...
Thanks for the response Ron, but that is the exact code I'm
using......just
modified for my circumstance.

Vaya con Dios,
Chuck, CABGx3



"Ron de Bruin" wrote:

Hi CLR

See this example that use
If rng2 Is Nothing Then

http://www.contextures.com/xlautofilter03.html#Copy


--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message
...
Hi All.......

I have a macro obtained long ago from this group......I keep re-using
it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that
reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it
worked when
I first got it, (maybe not, I almost never have no results on the
autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

Indeed that was the case Bob...........thanks for the response and the code,
I'll put it in my "special stash for neat stuff" place........

Vaya con Dios,
Chuck, CABGx3





"Bob Phillips" wrote:

Sounds like you don't have a filter on at all Chuck. You could test that

Sub CopyFilterInquiry()
Dim rng As Range
Dim rng2 As Range

Sheets("DATAINPUT").Select
If Not ActiveSheet.AutoFilterMode Then
MsgBox "No autofilter set"
Else
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select
End If
End Sub

--
HTH

Bob Phillips

"CLR" wrote in message
...
Thanks for the response Ron, but that is the exact code I'm
using......just
modified for my circumstance.

Vaya con Dios,
Chuck, CABGx3



"Ron de Bruin" wrote:

Hi CLR

See this example that use
If rng2 Is Nothing Then

http://www.contextures.com/xlautofilter03.html#Copy


--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message
...
Hi All.......

I have a macro obtained long ago from this group......I keep re-using
it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that
reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it
worked when
I first got it, (maybe not, I almost never have no results on the
autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

Hi CLR

Sorry I don't read your code
I see Bob have found your problem

--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message ...
Thanks for the response Ron, but that is the exact code I'm using......just
modified for my circumstance.

Vaya con Dios,
Chuck, CABGx3



"Ron de Bruin" wrote:

Hi CLR

See this example that use
If rng2 Is Nothing Then

http://www.contextures.com/xlautofilter03.html#Copy


--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message ...
Hi All.......

I have a macro obtained long ago from this group......I keep re-using it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it worked when
I first got it, (maybe not, I almost never have no results on the autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3

Not a problem Ron.......if my code had not been actually ok, your suggestion
would have been perfect........

Thanks for the response.....

Vaya con Dios,
Chuck, CABGx3



"Ron de Bruin" wrote:

Hi CLR

Sorry I don't read your code
I see Bob have found your problem

--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message ...
Thanks for the response Ron, but that is the exact code I'm using......just
modified for my circumstance.

Vaya con Dios,
Chuck, CABGx3



"Ron de Bruin" wrote:

Hi CLR

See this example that use
If rng2 Is Nothing Then

http://www.contextures.com/xlautofilter03.html#Copy


--
Regards Ron de Bruin
http://www.rondebruin.nl



"CLR" wrote in message ...
Hi All.......

I have a macro obtained long ago from this group......I keep re-using it
with modifications on new projects. It now works fine it there is any
results in the Autofilter, it fails when there is not, on line that reads,
"With ActiveSheet.AutoFilter.Range".......it seems to me that it worked when
I first got it, (maybe not, I almost never have no results on the autofilter)
probably I've "enhanced" it to death........help please?


Sub CopyFilterInquiry()

Dim rng As Range
Dim rng2 As Range
Sheets("DATAINPUT").Select
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Inquiry").[DataRangeInquiry].Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("Inquiry").Range("A9")
End If
Sheets("Inquiry").Select
Range("a9").Select

End Sub


TIA
Vaya con Dios,
Chuck, CABGx3