Thankyou Tom working Ok

Regards
Bob C.

"Tom Ogilvy" wrote:

It remains a bad editing job, the correction should have been:

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

--
Regards,
Tom Ogilvy



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

Taking the comma out allowed the line to run , but it's copying all the
columns and I only require the names in column B to copy across to the
second
sheet at Cell B2, B38, B74 etc.

Regards Bob C.

"Tom Ogilvy" wrote:

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.