I have a sheet which needs to display data in a specific
manner.
I have data in the following format
Store Code : Store Name : Region
AA : Bromley : 3
AB : Croydon : 3
etc....................
DE : Newcastle : 12
DG : Morpeth : 12
etc ...................

There are a total of 33 stores split into different
regions. I have a full list off screen and want to
populate the print area with only those from the region
the spread sheet is using to compare data sets. On the
cover the user selects the store code and the rest of the
sheet is populated with data from stores in the same
region pages 3 - 27. What I wanted was on the second page
to dynamicly show only the stores in the region being
examined. I have named each region as named areas.
i.e 'region3' 'region12' etc and named the cell where I
want the region pasted to or whown as 'regionpaste' What
I need to do is monitor Cell A70 to decide what region to
include in the printable area.
This may sound confusing but ask away if you don't
understand or mail me direct and I will send a copy of the
sheet to explain what I mean.
Any help would be usefull.

Nigel,

If I understand correctly, you have a data list, and you
want your user to click (double?) on a store, and then
have that store's data, along with the data for only
those stores within that Region displayed elsewhere.

Is there a specific reason why you need to past the data
elsewhere, as it would appear to me from what I read,
that you could simply apply a filter in place.

There are a couple of worksheet level events that will do
this for you, BeforeDoubleCLick would be my pick,
BeforeReightclick and SelectionChange would also work.

You can then use activecell.row to return the row(r), and
look for the value in R(r)C3 assuming your Region numbers
are in column C.

Once you have found that value, combine it with the
string "Region" to identify your predeifned Area and copy
the area to your paste range.

Helps?

Steve

-----Original Message-----
I have a sheet which needs to display data in a specific
manner.
I have data in the following format
Store Code : Store Name : Region
AA : Bromley : 3
AB : Croydon : 3
etc....................
DE : Newcastle : 12
DG : Morpeth : 12
etc ...................

There are a total of 33 stores split into different
regions. I have a full list off screen and want to
populate the print area with only those from the region
the spread sheet is using to compare data sets. On the
cover the user selects the store code and the rest of
the
sheet is populated with data from stores in the same
region pages 3 - 27. What I wanted was on the second
page
to dynamicly show only the stores in the region being
examined. I have named each region as named areas.
i.e 'region3' 'region12' etc and named the cell where I
want the region pasted to or whown as 'regionpaste'
What
I need to do is monitor Cell A70 to decide what region
to
include in the printable area.
This may sound confusing but ask away if you don't
understand or mail me direct and I will send a copy of
the
sheet to explain what I mean.
Any help would be usefull.
.

Use the Autofilter under Data=Filter=autofilter to make only those rows
from the specified region visible. You can use the change event in the
worksheet to react to a change in Cell A70 to change the criteria in the
autofilter.

Turn on the macro recorder and apply the filter, select the dropdow for
region and apply a criteria. Then turn off the macro recorder. Adapt the
code to use the value in A70 for the criteria.

--
Regards,
Tom Ogilvy

"Nigel Graham" wrote in message
...
I have a sheet which needs to display data in a specific
manner.
I have data in the following format
Store Code : Store Name : Region
AA : Bromley : 3
AB : Croydon : 3
etc....................
DE : Newcastle : 12
DG : Morpeth : 12
etc ...................

There are a total of 33 stores split into different
regions. I have a full list off screen and want to
populate the print area with only those from the region
the spread sheet is using to compare data sets. On the
cover the user selects the store code and the rest of the
sheet is populated with data from stores in the same
region pages 3 - 27. What I wanted was on the second page
to dynamicly show only the stores in the region being
examined. I have named each region as named areas.
i.e 'region3' 'region12' etc and named the cell where I
want the region pasted to or whown as 'regionpaste' What
I need to do is monitor Cell A70 to decide what region to
include in the printable area.
This may sound confusing but ask away if you don't
understand or mail me direct and I will send a copy of the
sheet to explain what I mean.
Any help would be usefull.