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

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

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

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

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