ExcelBanter - Copy Autofilter results macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Copy Autofilter results macro (https://www.excelbanter.com/excel-programming/329463-copy-autofilter-results-macro.html)

CLR

Copy Autofilter results macro

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

Ron de Bruin

Copy Autofilter results macro

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

CLR

Copy Autofilter results macro

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

CLR

Copy Autofilter results macro

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

Bob Phillips[_7_]

Copy Autofilter results macro

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

CLR

Copy Autofilter results macro

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

Ron de Bruin

Copy Autofilter results macro

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

CLR

Copy Autofilter results macro

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

All times are GMT +1. The time now is 06:28 AM.