Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fustration Help? Filter, Visible Copy/Paste
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 -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387968 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fustration Help? Filter, Visible Copy/Paste
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 --- Regards, Norman "Craigm" wrote in message ... 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 -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387968 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fustration Help? Filter, Visible Copy/Paste
Error "Object variable or With block variable not set" occurs he 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 -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387968 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fustration Help? Filter, Visible Copy/Paste
Hi Craig,
Error "Object variable or With block variable not set" occurs he 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) : --- Regards, Norman "Craigm" wrote in message ... Error "Object variable or With block variable not set" occurs he 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 -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387968 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fustration Help? Filter, Visible Copy/Paste
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 -------------------------------------- -- Craig ----------------------------------------------------------------------- Craigm's Profile: http://www.excelforum.com/member.php...fo&userid=2438 View this thread: http://www.excelforum.com/showthread.php?threadid=38796 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fustration Help? Filter, Visible Copy/Paste
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. --- Regards, Norman "Craigm" wrote in message ... 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) and 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: "Copy method of range class failed" on this line: rng1.Copy Destination:=destSh.Range("A1") Full code list below........this AutoFilter is killing me, Thanks for 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 --------------------------------------- -- Craigm ------------------------------------------------------------------------ Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381 View this thread: http://www.excelforum.com/showthread...hreadid=387968 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows and paste onto visible only | Excel Discussion (Misc queries) | |||
Copy and paste VISIBLE data | Excel Discussion (Misc queries) | |||
Copy only visible cells after filter is applied/ sum after filter | Excel Worksheet Functions | |||
Copy/Paste Visible cells only | Excel Discussion (Misc queries) | |||
copy and paste visible columns | Excel Discussion (Misc queries) |