Fustration Help? Filter, Visible Copy/Paste

[Craigm]

I have struggled for a couple of weeks with AutoFilter and the coping
and pasting of VISIBLE cells only to a different worksheet.

I continually get a crash on the paste method. I suspect that the
crash is caused during the copy but is delayed until the paste. When I
step through the code the copy works but I notice two "marching ants"
areas. One area is the AutoFilter itself and the other area is the
visible data. The paste puts the visible data into the new worksheet
then crashes. The AutoFilter area is not pasted but still both areas
have the “marching ants”.

The data starts on row 8 with the following filter on row seven:
Selection.AutoFilter Field:=13, Criteria1:=">12/31/2004",
Operator:=xlAnd _
, Criteria2:="<7/1/2005"

The filter's Critera1 seems to work even though the cells have data
like "1/9/2005 10:15:00 AM in them. Criteria2 seems to be ignored.

I have written loops, tried the visible cell types with no luck.
Select, resizing the range, paste. paste special...nothing is working
for me.

----Selection.SpecialCells(xlCellTypeVisible).Select
----Selection.SpecialCells(xlCellTypeVisible).Copy

The paste fails with "paste method of worksheet class failed".
"ActiveSheet.Paste" or Worksheets("Datadown").Range(srceRng).Copy
"Destination:=Worksheets("2005").Range(srceRng)" both have the same
result.

“With ActiveSheet.AutoFilter.Range” gives me an “Application defined or
object defined error.”

-----------------------------------------------------------
I have tried so many permutations that I cannot keep them straight.

Would some kind sole help me find sanity again with a code example that
I can learn from?

Respectfully, Craigm

 

[Norman Jones]

Hi Craim,

The following works for me:

Public Sub Tester02()
Dim rng As Range
Dim rng1 As Range

Dim srcSh As Worksheet
Dim destSh As Worksheet

With ActiveWorkbook
Set srcSh = .Sheets("Sheet1") '<<=== CHANGE
Set destSh = .Sheets("Sheet2") '<<=== CHANGE

End With

srcSh.Cells.AutoFilter _
Field:=1, _
Criteria1:=">12/31/2004", _
Operator:=xlAnd, _
Criteria2:="<=01/07/2005"

Set rng = srcSh.AutoFilter.Range
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

rng1.Copy Destination:=destSh.Range("A1")

End Sub

 

[Craigm]

Error "Object variable or With block variable not set" occurs here:
rng1.Copy Destination:=destSh.Range("A1")

Fix - Commenting out ",Operator:=xlAnd" and remaining AutoFilter line
lets the code run without error and copies the 2005 data to a seperate
worksheet. The code is on a single line without a continuation "_"
character.
srcSh.Cells.AutoFilter Field:=13, Criteria1:=">12/31/2004",
Operator:=xlAnd, Criteria2:="<=01/07/2005"

If I change the AutoFilter to the years 2003 & 2004 where I have more
data the filter is applied but no data is visible. All the data is
filtered out even though there is data for each month in the January -
June range. When you go into the filter the data is grouped by
month/year..."Jan-04", "Feb-04" and it will display if you manually
select it.


Thanks for helping with the code. The final solution is escaping me.

I'm hurting....What am I doing wrong?

Craigm

 

[Norman Jones]

Hi Craig,

Error "Object variable or With block variable not set" occurs here:
rng1.Copy Destination:=destSh.Range("A1")

This occurs because there is no autofilter extract range to copy.

To handle the error, change:

rng1.Copy Destination:=destSh.Range("A1")

to:

If Not rng1 Is Nothing Then
rng1.Copy Destination:=destSh.Range("A1")
End If

I'm hurting....What am I doing wrong?

If you want to send me your workbook, I will have a look. Replace any
sensitive data, but don't change *any* date information!


nXorman_jXones@btXconnectDOTcom

(replace dot and remove each X) :

 

[Craigm]

To establish the AutoFilter on row 7 (header row) I have added:
Rows("7:7").Select
Selection.AutoFilter

If I comment out the line below, I copy the AutoFilter row (Header) an
all the data.
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

When I change/comment out the above line above I get the error: "Cop
method of range class failed" on this line:
rng1.Copy Destination:=destSh.Range("A1")

Full code list below........this AutoFilter is killing me, Thanks fo
you kind guidance.

Respectfullt, Craigm
-------------------------------------------------------------------------------------

Public Sub Tester02 (sSheet As String)
'Copies filtered data (visible) to worksheet passed in.

Dim rng As Range
Dim rng1 As Range

Dim srcSh As Worksheet
Dim destSh As Worksheet

Worksheets(sSheet).Cells.Clear

Worksheets("DataDown").Activate

With ActiveWorkbook
Set srcSh = .Sheets("Datadown")
Set destSh = .Sheets(sSheet)
End With

'Establish the filter on row 7
Rows("7:7").Select
Selection.AutoFilter
srcSh.Cells.AutoFilter Field:=13, Criteria1:=">12/31/2003"
Operator:=xlAnd, Criteria2:="<=01/01/2005"


Set rng = srcSh.AutoFilter.Range

'This is wacking off the header in row 7
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)


On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng1 Is Nothing Then
rng1.Copy Destination:=destSh.Range("A1")
End If
End Sub

 

[Norman Jones]

Hi Craig,

Full code list below........this AutoFilter is killing me, Thanks for
you kind guidance.

As the suggested code runs without problem for me, and particularly bearing
in mind your experience with the second filter condition, I think that your
problems relate to peculiarities in your data configuration.

I have, therefore, already offered to look at your workbook if you want to
send it.