Hi

I have noticed in these posts the use of (xlCellTypeVisible) to select only
the filter data.
Can that be used to select only the filtered names in column A for copying
to a second sheet?

I have a list of 390 names in column A, a total of 12 room numbers in Column
L and a "Y" & "Z" code in column N.
I'm filtering the names by room number & a "Y" code.

The code I'm using at the moment is;

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B2")

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B38")

The Resize(400,1) is to ensure all names for a room are copied,
Question: What is the correct syntax to select only the visible names?

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

By default only the visible data is copied. You don't generally need to use
specialcells and xlCellTypeVisible when copying. It appears your code
should work. What problem are you having.

--
Regards,
Tom Ogilvy


"Robert Christie" wrote in message
...
Hi

I have noticed in these posts the use of (xlCellTypeVisible) to select
only
the filter data.
Can that be used to select only the filtered names in column A for copying
to a second sheet?

I have a list of 390 names in column A, a total of 12 room numbers in
Column
L and a "Y" & "Z" code in column N.
I'm filtering the names by room number & a "Y" code.

The code I'm using at the moment is;

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B2")

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B38")

The Resize(400,1) is to ensure all names for a room are copied,
Question: What is the correct syntax to select only the visible names?

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.

"Tom Ogilvy" wrote:

By default only the visible data is copied. You don't generally need to use
specialcells and xlCellTypeVisible when copying. It appears your code
should work. What problem are you having.

--
Regards,
Tom Ogilvy


"Robert Christie" wrote in message
...
Hi

I have noticed in these posts the use of (xlCellTypeVisible) to select
only
the filter data.
Can that be used to select only the filtered names in column A for copying
to a second sheet?

I have a list of 390 names in column A, a total of 12 room numbers in
Column
L and a "Y" & "Z" code in column N.
I'm filtering the names by room number & a "Y" code.

The code I'm using at the moment is;

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B2")

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B38")

The Resize(400,1) is to ensure all names for a room are copied,
Question: What is the correct syntax to select only the visible names?

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

--
Regards,
Tom Ogilvy

"Robert Christie" wrote in message
...
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names
is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.

Thanks Tom

And best wishes for the new Year.

Regards Bob C.


"Tom Ogilvy" wrote:

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

--
Regards,
Tom Ogilvy

"Robert Christie" wrote in message
...
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names
is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.

Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
on the line "set rng = rng.offset(1,0).Resize(rng.rows.count,-1)"


"Tom Ogilvy" wrote:

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

--
Regards,
Tom Ogilvy

"Robert Christie" wrote in message
...
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names
is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.

bad editing job on my part.

set rng = rng.offset(1,0).Resize(rng.rows.count,-1)

should have no comma preceding the -1

set rng = rng.offset(1,0).Resize(rng.rows.count-1)

--
Regards,
Tom Ogilvy

"Robert Christie" wrote in message
...
Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
on the line "set rng = rng.offset(1,0).Resize(rng.rows.count,-1)"


"Tom Ogilvy" wrote:

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2 )

--
Regards,
Tom Ogilvy

"Robert Christie" wrote in message
...
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure
all
names are copied, ( number of names per room differ). Each group of
names
is
copied to the same second sheet at different positions down column B.
I'm
starting at the top and working down the column so as not to
overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the
resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.