Grouping & Sorting question

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

Here is what I wish to eventually achieve, and would be
grateful for some help with the first part:

User chooses a workbook, and in each sheet in col G
they will 'mark' any row they choose with a value from
A to Z (Later they will be able to do the same in cols
H and I).

I'm going to sort and group in sheets in a new workbook
based on the values in col G only (for now), such that
all the rows marked 'A' end up in sheet 'A' in the new book
etc.

So ideally the code would run, then pause to allow the
user to enter their 'A', 'B's etc after which the code
would present a form (ideally) containing the unique
values in col G against which the user could input the
'true' name (say 'A' represented Timber), such that
a new sheet could be named "Timber" where all
'A's could then be placed.

If that makes sense, then how best to pause the code,
and what is the best control for the form, please?
Or is there a better way?

Regards.
 
The best way would to be able to identify those lines that pertain to Timber
without having the user designate which ones they are - how is the user
making the determination? Could the code do the same thing?

You really can't make the macro pause. You can end the macro and have the
user kick off the next step. If it was a single cell, you could kludge
something with onkey, but since the user basically needs to make multiple
entries, the user would have to decide when they are done and perform some
action that starts a new macro to complete the job. If you want to maintain
control, then you would need to present the pertinent information in a
userform and not give the user uncontrolled access to the sheet.
 
Thanks for the reply. I was unsure how to approach this
task.

Here is a typical data row, where User's original data is
in cols B, C and D. The additional columns/data etc have
been created via addin routines:
'headers'
Item Description Qty Unit Rate Total col 'G'
'data'
A Carpets 10 m2

User would enter 'F' in col G (for Flooring), the idea being that
it and all further 'F' items were saved to a common sheet. The
same would apply for slate flooring, vinyl flooring, etc. However
I think it has to be user-designated, rather than looking for
'keywords' in col B.

The idea for cols H and I is as follows:
col G: go to a carpet shop and get a price to buy the carpet
col H: go to a carpet shop and get a price to supply and fit
the carpet
col I: go to the carpet shop and get a price to fit only the
user's own carpet
Banaal I know(g) and Saturday too!

So after the save to a sheet, I'm hoping to offer the user the
options to print/fax/email their col G, H ,I options to a shop
or subcontractor, for pricing the various options. Only certain
options are sent to specific shops/subcontractors.

Is this feasible/sensible?
I'm trying to keep this within VBA control, but at worst,
obviously the 'sort' could be achieved in pure Excel.

Users + me using Excel 2000.

Regards.
 
As I see it:
macro ends
User completes columns G, H, I
User starts macro to process the data.

Alternative is to loop through the items row at a time, load into a
userform, user makes selection there, write results to row, do next entry.
 
Many thanks for the help.

Regards.

Tom Ogilvy said:
As I see it:
macro ends
User completes columns G, H, I
User starts macro to process the data.

Alternative is to loop through the items row at a time, load into a
userform, user makes selection there, write results to row, do next entry.
Click to expand...
 
Back
Top