Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - allowing for error
Using the code below provide by Tom Olgivy to copy & paste filtered data to a second sheet. Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") How do I more to next Criteria:=06 if 05 not found in Field:=12 -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - allowing for error
Let me guess at everything else
for i = 5 to 6 Set rng = Sheets("Names").AutoFilter.Range rng.Select Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) if Application.Countif(rng.columns(12),format(i,"00") ) 0 then rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00").Range("A2") Selection.AutoFilter Field:=12, Criteria1:=format(i,"00") rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00")).Range("A38") exit for End if Next i -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Using the code below provide by Tom Olgivy to copy & paste filtered data to a second sheet. Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") How do I more to next Criteria:=06 if 05 not found in Field:=12 -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - allowing for error
Sorry about last post Tom
You did a great job deciphering my question, I'd be lucky to understand it, myself. You answered what I was trying to ask in my post: i.e. If filtering produces a zero result skip to next criteria in field 12. I have since realised my approach to what I need to achieve is wrong, a complete re-think required. The school has a total of 16 classrooms starting at #04 up to #19 The number of Classrooms required & actual Class numbers used, varies from year to year dependant on number of students enrolled and age group figures. This year a total of 11 Classroom Numbers are in use. Room numbers this year are #04 to 06, 08 to 12, 15, 18, and 19. The Range destinations in code below i.e. A2, A38, A74 etc increment by 36 rows. Can all the values in column 12 be reduced down to a unique list in memory to be called up as required in the filter operation and room names copied to range destinations A2, A38, A74 €¦€¦.A398? or Can all 16 room numbers be temporary listed in a helper column or held in memory to be called up as required, and the first rooms names (in this case room 04) be copied to A2 and next room 05 to A38 (A2+36 rows)? And, if room 07 is not used have room 08 names move up to A110 and not A146? Can subsequent room names move up 36 rows until next non-used room number is found and so on? Code at present: ''' Transfer Class No.4 names only. Selection.AutoFilter Field:=14, Criteria1:="Y" €˜ Enrolled students names only Selection.AutoFilter Field:=12, Criteria1:="04" Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") ''' Transfer Class No.6 names only. Selection.AutoFilter Field:=12, Criteria1:="06" rng.Copy Destination:=Worksheets("Feb Results05").Range("A74") ''' Transfer Class No.7 names only. Selection.AutoFilter Field:=12, Criteria1:="07" rng.Copy Destination:=Worksheets("Feb Results05").Range("A110") ''' Transfer Class No. 6 names only. Selection.AutoFilter Field:=12, Criteria1:="08" rng.Copy Destination:=Worksheets("Feb Results05").Range("A146") Hope this is clearer. TIA Regards Aussie Bob C. "Tom Ogilvy" wrote: Let me guess at everything else for i = 5 to 6 Set rng = Sheets("Names").AutoFilter.Range rng.Select Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) if Application.Countif(rng.columns(12),format(i,"00") ) 0 then rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00").Range("A2") Selection.AutoFilter Field:=12, Criteria1:=format(i,"00") rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00")).Range("A38") exit for End if Next i -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Using the code below provide by Tom Olgivy to copy & paste filtered data to a second sheet. Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") How do I more to next Criteria:=06 if 05 not found in Field:=12 -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - allowing for error
This will copy only data for classrooms in use (as shown in column L of
your database) and sequentially place each classrooms roster at 36 row intervals starting in row 2. Tested successfully on data as I pictured it from your code. Sub ABEE() Dim i As Long, k As Long Dim rng As Range, rng1 As Range Dim rng2 As Range Sheets("Names").Range("A1").CurrentRegion _ .AutoFilter Field:=14, Criteria1:="Y" Set rng = Sheets("Names").AutoFilter.Range Set rng2 = rng Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) Set rng1 = rng.Columns(12).Cells k = 2 For i = 4 To 19 s = Format(i, "00") If Application.CountIf(rng1, s) 0 Then rng2.AutoFilter Field:=12, Criteria1:=Format(i, "00") rng.Copy Destination:=Worksheets("Feb Results05").Cells(k, 1) k = k + 36 End If Next i End Sub -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Sorry about last post Tom You did a great job deciphering my question, I'd be lucky to understand it, myself. You answered what I was trying to ask in my post: i.e. If filtering produces a zero result skip to next criteria in field 12. I have since realised my approach to what I need to achieve is wrong, a complete re-think required. The school has a total of 16 classrooms starting at #04 up to #19 The number of Classrooms required & actual Class numbers used, varies from year to year dependant on number of students enrolled and age group figures. This year a total of 11 Classroom Numbers are in use. Room numbers this year are #04 to 06, 08 to 12, 15, 18, and 19. The Range destinations in code below i.e. A2, A38, A74 etc increment by 36 rows. Can all the values in column 12 be reduced down to a unique list in memory to be called up as required in the filter operation and room names copied to range destinations A2, A38, A74 ...A398? or Can all 16 room numbers be temporary listed in a helper column or held in memory to be called up as required, and the first rooms names (in this case room 04) be copied to A2 and next room 05 to A38 (A2+36 rows)? And, if room 07 is not used have room 08 names move up to A110 and not A146? Can subsequent room names move up 36 rows until next non-used room number is found and so on? Code at present: ''' Transfer Class No.4 names only. Selection.AutoFilter Field:=14, Criteria1:="Y" ' Enrolled students names only Selection.AutoFilter Field:=12, Criteria1:="04" Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") ''' Transfer Class No.6 names only. Selection.AutoFilter Field:=12, Criteria1:="06" rng.Copy Destination:=Worksheets("Feb Results05").Range("A74") ''' Transfer Class No.7 names only. Selection.AutoFilter Field:=12, Criteria1:="07" rng.Copy Destination:=Worksheets("Feb Results05").Range("A110") ''' Transfer Class No. 6 names only. Selection.AutoFilter Field:=12, Criteria1:="08" rng.Copy Destination:=Worksheets("Feb Results05").Range("A146") Hope this is clearer. TIA Regards Aussie Bob C. "Tom Ogilvy" wrote: Let me guess at everything else for i = 5 to 6 Set rng = Sheets("Names").AutoFilter.Range rng.Select Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) if Application.Countif(rng.columns(12),format(i,"00") ) 0 then rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00").Range("A2") Selection.AutoFilter Field:=12, Criteria1:=format(i,"00") rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00")).Range("A38") exit for End if Next i -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Using the code below provide by Tom Olgivy to copy & paste filtered data to a second sheet. Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") How do I more to next Criteria:=06 if 05 not found in Field:=12 -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - allowing for error
Tom
Thank you for replying, code works great. What part of code actually selects column A fromA2 down for copying? Could code be modified to select say columns A to F from 2nd row?. I have a similiar copying requirement on a student test results sheet. Thanks again for you help Regards, Aussie Bob C. "Tom Ogilvy" wrote: This will copy only data for classrooms in use (as shown in column L of your database) and sequentially place each classrooms roster at 36 row intervals starting in row 2. Tested successfully on data as I pictured it from your code. Sub ABEE() Dim i As Long, k As Long Dim rng As Range, rng1 As Range Dim rng2 As Range Sheets("Names").Range("A1").CurrentRegion _ .AutoFilter Field:=14, Criteria1:="Y" Set rng = Sheets("Names").AutoFilter.Range Set rng2 = rng Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) Set rng1 = rng.Columns(12).Cells k = 2 For i = 4 To 19 s = Format(i, "00") If Application.CountIf(rng1, s) 0 Then rng2.AutoFilter Field:=12, Criteria1:=Format(i, "00") rng.Copy Destination:=Worksheets("Feb Results05").Cells(k, 1) k = k + 36 End If Next i End Sub -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Sorry about last post Tom You did a great job deciphering my question, I'd be lucky to understand it, myself. You answered what I was trying to ask in my post: i.e. If filtering produces a zero result skip to next criteria in field 12. I have since realised my approach to what I need to achieve is wrong, a complete re-think required. The school has a total of 16 classrooms starting at #04 up to #19 The number of Classrooms required & actual Class numbers used, varies from year to year dependant on number of students enrolled and age group figures. This year a total of 11 Classroom Numbers are in use. Room numbers this year are #04 to 06, 08 to 12, 15, 18, and 19. The Range destinations in code below i.e. A2, A38, A74 etc increment by 36 rows. Can all the values in column 12 be reduced down to a unique list in memory to be called up as required in the filter operation and room names copied to range destinations A2, A38, A74 ...A398? or Can all 16 room numbers be temporary listed in a helper column or held in memory to be called up as required, and the first rooms names (in this case room 04) be copied to A2 and next room 05 to A38 (A2+36 rows)? And, if room 07 is not used have room 08 names move up to A110 and not A146? Can subsequent room names move up 36 rows until next non-used room number is found and so on? Code at present: ''' Transfer Class No.4 names only. Selection.AutoFilter Field:=14, Criteria1:="Y" ' Enrolled students names only Selection.AutoFilter Field:=12, Criteria1:="04" Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") ''' Transfer Class No.6 names only. Selection.AutoFilter Field:=12, Criteria1:="06" rng.Copy Destination:=Worksheets("Feb Results05").Range("A74") ''' Transfer Class No.7 names only. Selection.AutoFilter Field:=12, Criteria1:="07" rng.Copy Destination:=Worksheets("Feb Results05").Range("A110") ''' Transfer Class No. 6 names only. Selection.AutoFilter Field:=12, Criteria1:="08" rng.Copy Destination:=Worksheets("Feb Results05").Range("A146") Hope this is clearer. TIA Regards Aussie Bob C. "Tom Ogilvy" wrote: Let me guess at everything else for i = 5 to 6 Set rng = Sheets("Names").AutoFilter.Range rng.Select Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) if Application.Countif(rng.columns(12),format(i,"00") ) 0 then rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00").Range("A2") Selection.AutoFilter Field:=12, Criteria1:=format(i,"00") rng.Copy Destination:=Worksheets("Feb Results" & format(i,"00")).Range("A38") exit for End if Next i -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Using the code below provide by Tom Olgivy to copy & paste filtered data to a second sheet. Set rng = Sheets("Names").AutoFilter.Range Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) rng.Copy Destination:=Worksheets("Feb Results05").Range("A2") ''' Transfer Class No.5 names only. Selection.AutoFilter Field:=12, Criteria1:="05" rng.Copy Destination:=Worksheets("Feb Results05").Range("A38") How do I more to next Criteria:=06 if 05 not found in Field:=12 -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Autofilter Error | Excel Discussion (Misc queries) | |||
Excel Autofilter error | Excel Discussion (Misc queries) | |||
Copy error with Autofilter | Excel Programming | |||
autofilter macro causes #VALUE! error | Excel Programming | |||
VBA Autofilter error in '97, not 2000 | Excel Programming |