Filter Copy/Paste Fails - Two Ranges Selected?

[Craigm]

The Autofilter is turned on in the code below.

The copy/paste work (the data is transfered to another worksheet) but
the macro crashes on "WorkSheets("2005").PasteP - with the error
message: "Paste method of worksheet class failed."

When I look at the source worksheet I can see the "marching ants"
around two seperate ranges.

One range is the row that the filter itself is in (rows 2 through 7).
The filter is in Row 7. Rows 1 thorugh 6 are unneeded header
information.

The second range is the data that I am copy/pasting (and it works).

I have killed myself on this one for two days. Can anyone help this
undeserving Newbe?

I also need to find the last row of filtered data not hard code the
range (A2:Z157).

Craigm

---------------------------------
Sub Data_05()

Dim srceRng As Range
Dim destRng As Range
Dim i As Integer

'ActiveWindow.Visible = False
Worksheets("2005").Cells.Clear
Windows("060631 Charts_DataDown 3.xls").Activate
Sheets("Datadown").Select
Range("J7").Activate
Selection.AutoFilter Field:=13, Criteria1:=">12/31/2004",
Operator:=xlAnd _
, Criteria2:="<7/1/2005"
Range("A1").Activate

'need to find the last row for the range not hard code it.
Set srceRng = Workbooks("060631 Charts_DataDown
3.xls").Sheets("Datadown").Range("A2:Z157")
srceRng.Copy

Sheets("2005").Select
Worksheets("2005").Activate

'ActiveSheet.Paste Destination:=Worksheets("2005").Range("A1")
Range("A1").Select
'ActiveSheet.Paste
'Crashes here but data is pasted
Worksheets("2005").Paste '****** Crashes here every time! ******

'Turn the filter off
Sheets("Datadown").Select
Rows("7:7").Select
Range("J7").Activate
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=1
Selection.AutoFilter

End Sub

 

[Craigm]

Set srceRng = Range("A8:z157")
srceRng.Select
srceRng.Copy

Now I need to select only the visible rows to copy and eliminate th
hard coding. This skill has been escaping me too.

Thanks

Cria

 

[Guest]

ActiveSheet.Cells.Cells.SpecialCells(xlCellTypeVisible).Select has worked for
me.

 

[Norman Jones]

Hi Craig,

Set srceRng = Range("A8:z157")
srceRng.Select
srceRng.Copy

Now I need to select only the visible rows to copy and eliminate the
hard coding. This skill has been escaping me too.

Try something like:

Sub TestIt()

Dim Rng1 As Range, Rng2 As Range
Dim srceRng As Range
Dim destRng As Range

Set destRng = ActiveSheet.Range("H200") '<<=== CHANGE
Set Rng1 = ActiveSheet.AutoFilter.Range
Set Rng2 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1)

Set srceRng = Rng2.SpecialCells(xlCellTypeVisible)
srceRng.Copy Destination:=destRng

End Sub

 

[Craigm]

Thanks for your suggestions and help.

I would have never got this one to work correctly by myself.

Craigm