Copy Autofilter results macro

G

Guest

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
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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

Vaya con Dios,
Chuck, CABGx3
 
B

Bob Phillips

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 said:
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 said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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



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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron de Bruin

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating sheet and copying filtered rows in it 3
Copying Filtered Data from multiple worksheets 3
Copy Filtered Rows 5
how to autofilter for the remaining values???? 1
Autofilter with Range problem 1
Filter, Delete and Error handling - need help 4
autofilter copy to new range 3
Run time error '91 2

Top