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