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 the hard coding. This skill has been escaping me too.

Thanks

Criag

[Shawn]

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

"Craigm" wrote:

>
> 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.
>
> Thanks
>
> Criag
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=387482
>
>

[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

---
Regards,
Norman

[Craigm]

Thanks for your suggestions and help.

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

Craigm