Hi everyone and thanks for taking a look and for generally increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a column and
successfully made the minor alterations to get it to work, as a start.

However I don't want all unique values I only want them according to
named areas on a separate sheet "DATA" I have and intend to hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining O1:08 (CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain appears to
have melted.

I got as far as creating the sheet with the right name, but code tries to
take the filtered data from the "DATA" worksheet instead of the one
defined.

<rant I've been database programming since about 1984 and could do this
in an hour with a few SQL statements in visual Foxpro, but no-one wants it
since MickeySoft have killed a perfectly good language.</rant

Is this enough info to point me in the right direction? Or do I have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like me is the most
impressive thing I have seen about excel. I can't keep pace with the
traffic in here.

regards, Alan

Hi Alan

You can use application.match to test the unique value against the data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




. uk wrote in message
...
Hi everyone and thanks for taking a look and for generally increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a column and
successfully made the minor alterations to get it to work, as a start.

However I don't want all unique values I only want them according to
named areas on a separate sheet "DATA" I have and intend to hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining O1:08 (CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain appears to
have melted.

I got as far as creating the sheet with the right name, but code tries to
take the filtered data from the "DATA" worksheet instead of the one
defined.

<rant I've been database programming since about 1984 and could do this
in an hour with a few SQL statements in visual Foxpro, but no-one wants it
since MickeySoft have killed a perfectly good language.</rant

Is this enough info to point me in the right direction? Or do I have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like me is the most
impressive thing I have seen about excel. I can't keep pace with the
traffic in here.

regards, Alan

Thank you for the pointer, I will try and make sense of it!

regards, Alan

*From:* "Ron de Bruin"
*Date:* Mon, 31 Aug 2009 21:49:20 +0200

Hi Alan

You can use application.match to test the unique value against the
data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




. uk
wrote in message
...
Hi everyone and thanks for taking a look and for generally
increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a column and
successfully made the minor alterations to get it to work, as a
start.

However I don't want all unique values I only want them according
to
named areas on a separate sheet "DATA" I have and intend to hide
before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with
headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining O1:08
(CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in the
source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in
values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain
appears to
have melted.

I got as far as creating the sheet with the right name, but code
tries to
take the filtered data from the "DATA" worksheet instead of the
one
defined.

<rant I've been database programming since about 1984 and could
do this
in an hour with a few SQL statements in visual Foxpro, but no-one
wants it
since MickeySoft have killed a perfectly good language.</rant

Is this enough info to point me in the right direction? Or do I
have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like me is
the most
impressive thing I have seen about excel. I can't keep pace with
the
traffic in here.

Hi Ron,

Well, I'm afraid I couldn't see where your suggested code might fit.
However I now have your amended code *partly* working.

I can create each new sheet (as per Sheetnames list below) and populate
them with *part* of the data required.

I can parse out, for example the left and right portions of the list
"CodeNames". So the first criteria for worksheet "Cash" is "CSH" and the
second is "ATM". That works.

Where there is only one "code" that works too, but the last in the list
has 5 codes and that is the stumbling block (for me).

How can I do the autofilter with several criteria (on the one field)? I
have found something called a filter collection which sounds like it might
do the trick, but the MS examples don't enlighten me as to how I might
incorporate them.

Is it possible? or do I have to do them all separately and concatenate all
the data blocks before finally sorting on a different (date) field. That
way seems inelegant somehow.

Not only that but ISTM that if there were to be changes, the code would
have to be altered instead of just changing the named areas as required.

I am being pressured into getting this done soon. I know it can be done, I
just want it to be relatively maintenance free afterwards.

regards, Alan

In article ,
() wrote:

*From:*

*Date:* Tue, 01 Sep 2009 02:51:44 -0500

Thank you for the pointer, I will try and make sense of it!

regards, Alan

*From:* "Ron de Bruin"
*Date:* Mon, 31 Aug 2009 21:49:20 +0200

Hi Alan

You can use application.match to test the unique value against
the data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm






. uk
wrote in message
...
Hi everyone and thanks for taking a look and for generally
increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a column
and
successfully made the minor alterations to get it to work, as a
start.

However I don't want all unique values I only want them
according to
named areas on a separate sheet "DATA" I have and intend to
hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with
headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining O1:08
(CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in
the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in
values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain
appears to
have melted.

I got as far as creating the sheet with the right name, but
code tries to
take the filtered data from the "DATA" worksheet instead of the
one
defined.

<rant I've been database programming since about 1984 and
could do this
in an hour with a few SQL statements in visual Foxpro, but
no-one wants it
since MickeySoft have killed a perfectly good language.</rant

Is this enough info to point me in the right direction? Or do I
have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like me
is the most
impressive thing I have seen about excel. I can't keep pace
with the
traffic in here.


regards, Alan

Hi Alan

You can send me a small workbook and tell me what you want
and I try to look at it for you this week

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




. uk wrote in message
...
Hi Ron,

Well, I'm afraid I couldn't see where your suggested code might fit.
However I now have your amended code *partly* working.

I can create each new sheet (as per Sheetnames list below) and populate
them with *part* of the data required.

I can parse out, for example the left and right portions of the list
"CodeNames". So the first criteria for worksheet "Cash" is "CSH" and the
second is "ATM". That works.

Where there is only one "code" that works too, but the last in the list
has 5 codes and that is the stumbling block (for me).

How can I do the autofilter with several criteria (on the one field)? I
have found something called a filter collection which sounds like it might
do the trick, but the MS examples don't enlighten me as to how I might
incorporate them.

Is it possible? or do I have to do them all separately and concatenate all
the data blocks before finally sorting on a different (date) field. That
way seems inelegant somehow.

Not only that but ISTM that if there were to be changes, the code would
have to be altered instead of just changing the named areas as required.

I am being pressured into getting this done soon. I know it can be done, I
just want it to be relatively maintenance free afterwards.

regards, Alan

In article ,
() wrote:

*From:*

*Date:* Tue, 01 Sep 2009 02:51:44 -0500

Thank you for the pointer, I will try and make sense of it!

regards, Alan

*From:* "Ron de Bruin"
*Date:* Mon, 31 Aug 2009 21:49:20 +0200

Hi Alan

You can use application.match to test the unique value against
the data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm






. uk
wrote in message
...
Hi everyone and thanks for taking a look and for generally
increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a column
and
successfully made the minor alterations to get it to work, as a
start.

However I don't want all unique values I only want them
according to
named areas on a separate sheet "DATA" I have and intend to
hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with
headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining O1:08
(CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in
the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in
values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain
appears to
have melted.

I got as far as creating the sheet with the right name, but
code tries to
take the filtered data from the "DATA" worksheet instead of the
one
defined.

<rant I've been database programming since about 1984 and
could do this
in an hour with a few SQL statements in visual Foxpro, but
no-one wants it
since MickeySoft have killed a perfectly good language.</rant

Is this enough info to point me in the right direction? Or do I
have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like me
is the most
impressive thing I have seen about excel. I can't keep pace
with the
traffic in here.


regards, Alan

Ron, that is incredibly generous of your time. Thank you very much.

However I am very pig headed and NEED to learn this stuff (my wife thinks
I am nuts working on this in the evenings till VERY late - because this is
a separate contract to my normal daytime work). I believe (after another
10 hours at it!) that I THINK I have a sight of a solution and will
persist for a day or two longer.

With your permission I will send you what I have for criticism when I have
exhausted what brains I have. I will be prepared to be humbled. :-D

It is very irritating knowing what you want to do, but lacking the
language & syntax knowledge. Also irritating is that VBA has similar
functions to Excel but with different syntax.

Thanks again, it's great to have a backstop.

regards, Alan

In article ,
(Ron de Bruin) wrote:

*From:* "Ron de Bruin"
*Date:* Thu, 3 Sep 2009 21:49:59 +0200

Hi Alan

You can send me a small workbook and tell me what you want
and I try to look at it for you this week

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




. uk
wrote in message
...
Hi Ron,

Well, I'm afraid I couldn't see where your suggested code might
fit.
However I now have your amended code *partly* working.

I can create each new sheet (as per Sheetnames list below) and
populate
them with *part* of the data required.

I can parse out, for example the left and right portions of the
list
"CodeNames". So the first criteria for worksheet "Cash" is "CSH"
and the
second is "ATM". That works.

Where there is only one "code" that works too, but the last in
the list
has 5 codes and that is the stumbling block (for me).

How can I do the autofilter with several criteria (on the one
field)? I
have found something called a filter collection which sounds like
it might
do the trick, but the MS examples don't enlighten me as to how I
might
incorporate them.

Is it possible? or do I have to do them all separately and
concatenate all
the data blocks before finally sorting on a different (date)
field. That
way seems inelegant somehow.

Not only that but ISTM that if there were to be changes, the code
would
have to be altered instead of just changing the named areas as
required.

I am being pressured into getting this done soon. I know it can
be done, I
just want it to be relatively maintenance free afterwards.

regards, Alan

In article ,

() wrote:

*From:*

*Date:* Tue, 01 Sep 2009 02:51:44 -0500

Thank you for the pointer, I will try and make sense of it!

regards, Alan

*From:* "Ron de Bruin"
*Date:* Mon, 31 Aug 2009 21:49:20 +0200

Hi Alan

You can use application.match to test the unique value against
the data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm







. uk
wrote in message
...
Hi everyone and thanks for taking a look and for generally
increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a
column
and
successfully made the minor alterations to get it to work,
as a
start.

However I don't want all unique values I only want them
according to
named areas on a separate sheet "DATA" I have and intend to
hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with
headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining
O1:08
(CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in
the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in
values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain
appears to
have melted.

I got as far as creating the sheet with the right name, but
code tries to
take the filtered data from the "DATA" worksheet instead of
the
one
defined.

<rant I've been database programming since about 1984 and
could do this
in an hour with a few SQL statements in visual Foxpro, but
no-one wants it
since MickeySoft have killed a perfectly good
language.</rant

Is this enough info to point me in the right direction? Or
do I
have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like
me
is the most
impressive thing I have seen about excel. I can't keep pace
with the
traffic in here.


regards, Alan



regards, Alan

With your permission I will send you what I have for criticism when I have
exhausted what brains I have. I will be prepared to be humbled. :-D


No problem

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




. uk wrote in message
...
Ron, that is incredibly generous of your time. Thank you very much.

However I am very pig headed and NEED to learn this stuff (my wife thinks
I am nuts working on this in the evenings till VERY late - because this is
a separate contract to my normal daytime work). I believe (after another
10 hours at it!) that I THINK I have a sight of a solution and will
persist for a day or two longer.

With your permission I will send you what I have for criticism when I have
exhausted what brains I have. I will be prepared to be humbled. :-D

It is very irritating knowing what you want to do, but lacking the
language & syntax knowledge. Also irritating is that VBA has similar
functions to Excel but with different syntax.

Thanks again, it's great to have a backstop.

regards, Alan

In article ,
(Ron de Bruin) wrote:

*From:* "Ron de Bruin"
*Date:* Thu, 3 Sep 2009 21:49:59 +0200

Hi Alan

You can send me a small workbook and tell me what you want
and I try to look at it for you this week

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




. uk
wrote in message
...
Hi Ron,

Well, I'm afraid I couldn't see where your suggested code might
fit.
However I now have your amended code *partly* working.

I can create each new sheet (as per Sheetnames list below) and
populate
them with *part* of the data required.

I can parse out, for example the left and right portions of the
list
"CodeNames". So the first criteria for worksheet "Cash" is "CSH"
and the
second is "ATM". That works.

Where there is only one "code" that works too, but the last in
the list
has 5 codes and that is the stumbling block (for me).

How can I do the autofilter with several criteria (on the one
field)? I
have found something called a filter collection which sounds like
it might
do the trick, but the MS examples don't enlighten me as to how I
might
incorporate them.

Is it possible? or do I have to do them all separately and
concatenate all
the data blocks before finally sorting on a different (date)
field. That
way seems inelegant somehow.

Not only that but ISTM that if there were to be changes, the code
would
have to be altered instead of just changing the named areas as
required.

I am being pressured into getting this done soon. I know it can
be done, I
just want it to be relatively maintenance free afterwards.

regards, Alan

In article ,

() wrote:

*From:*

*Date:* Tue, 01 Sep 2009 02:51:44 -0500

Thank you for the pointer, I will try and make sense of it!

regards, Alan

*From:* "Ron de Bruin"
*Date:* Mon, 31 Aug 2009 21:49:20 +0200

Hi Alan

You can use application.match to test the unique value against
the data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm







. uk
wrote in message
...
Hi everyone and thanks for taking a look and for generally
increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a
column
and
successfully made the minor alterations to get it to work,
as a
start.

However I don't want all unique values I only want them
according to
named areas on a separate sheet "DATA" I have and intend to
hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with
headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining
O1:08
(CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in
the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in
values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain
appears to
have melted.

I got as far as creating the sheet with the right name, but
code tries to
take the filtered data from the "DATA" worksheet instead of
the
one
defined.

<rant I've been database programming since about 1984 and
could do this
in an hour with a few SQL statements in visual Foxpro, but
no-one wants it
since MickeySoft have killed a perfectly good
language.</rant

Is this enough info to point me in the right direction? Or
do I
have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like
me
is the most
impressive thing I have seen about excel. I can't keep pace
with the
traffic in here.


regards, Alan



regards, Alan

Nearly there I think (phew!) I'm quite pleased that it all (seems to) work
up to here.

1. Do you still want to look at it? (maybe give you a laugh!) I have one
or two "cheats" in there because of time constraints.
2. If so do I send it to the address here?
3 It will demonstrate better as the whole spreadsheet (408Kb) will that be
OK?

my addy is a_pengelly_ampersand_cix.co.uk

(Remove underscores and replace ampersand with @)

In article ,
(Ron de Bruin) wrote:

*From:* "Ron de Bruin"
*Date:* Fri, 4 Sep 2009 22:06:06 +0200

With your permission I will send you what I have for criticism
when I have
exhausted what brains I have. I will be prepared to be humbled. :-D


No problem


regards, Alan