Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Copy/Paste Fails - Two Ranges Selected?
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 ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387482 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Copy/Paste Fails - Two Ranges Selected?
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 -- Craig ----------------------------------------------------------------------- Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438 View this thread: http://www.excelforum.com/showthread.php?threadid=38748 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Copy/Paste Fails - Two Ranges Selected?
ActiveSheet.Cells.Cells.SpecialCells(xlCellTypeVis ible).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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Copy/Paste Fails - Two Ranges Selected?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Copy/Paste Fails - Two Ranges Selected?
Thanks for your suggestions and help. I would have never got this one to work correctly by myself. Craigm -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387482 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy selected rows to second worksheet (NOT Cut + Paste) | New Users to Excel | |||
How can I copy big ranges of cells without drag or copy/paste? | Excel Discussion (Misc queries) | |||
Why Copy/Paste fails using Offset & Resize of myRange? | Excel Discussion (Misc queries) | |||
copy/paste selected worksheets | Excel Programming | |||
How to Copy & Paste selected Range in Excel (MFC) | Excel Programming |