Selecting Column of Visible AutoFiltered Cells.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>