Best way to make column lists out of row of cells?
 

I need to create a header row in a new worksheet based on user
choices. I have all the possible choices in one "master" row - each
cell is already formatted for color, border, column width, font, etc,
and the program will take the ones chosen by the user and copy them
onto a new sheet. Data will later be added, so getting the proper
headers up front is a bit important.

The individual column headings can be separated into a couple of
different data categories, and there is always the possibility that a
user will want choices not yet there that fit a different category.
The question I have is how to list all these headings in different
columns based on their data category. The data category for each
individual heading would be contained in a comment in that cell.

The complete composite header row stretches from A1 to AD1 at the
moment; more will likely be added later. I'd like to go to CA1, put
the data category name, and write all headers for that category in CA2
and down; go to CC1, put the next category name, all headers for that
category in CC2 and down; lather, rinse, repeat for all categories.
All this would be in a Workbook_Open sub.

My general fuzzy idea goes something like this:
-- Iterate through the cells of the whole header row.
-- Read the comment text of each cell to get the category.
-- Write the text of the header cell in the column for the proper
category.

The tough part is not knowing how many categories there are or what
their names are, because things will get added as we go along. That
means I have to track which categories have been created; if this next
one has not been created yet, then go to the next column over and
create it, then write the header under it. (The user will then scan
these lists, put an X by the desired headers, and click a button to
build the desired header row n a new worksheet. That part I think I
can handle.)

Do I create an array variable on the fly for each category, and then
iterate through all my arrays to write each one to the worksheet? Is
there a better way to track "categories created" so I know when I hit
a new one? How would y'all approach this?

Ed
(Working in XL2003)

Best way to make column lists out of row of cells?
 

I would do it in rows rather than in columns.

I would make column A the category and put the category name in the first
row only of each category. Then you can search down column A and skip blank
calell to find each category

The in column B I would put the column letter where the category will go.

The in column C I would put the heading name with the proper format.

I would also rather than use the comments put additional info in differnt
column (D, E, f, ...)

I would make a listbox and put the categories into the list box alphetized.
You can use a userform and two list boxes. When the category is selected in
the first box display all the headings in the 2nd box. This is a little bit
more complicated but the results really gets peoples attention.

I did something like this for another posting this week and it turned out
really great.
"Ed from AZ" wrote:

I need to create a header row in a new worksheet based on user
choices. I have all the possible choices in one "master" row - each
cell is already formatted for color, border, column width, font, etc,
and the program will take the ones chosen by the user and copy them
onto a new sheet. Data will later be added, so getting the proper
headers up front is a bit important.

The individual column headings can be separated into a couple of
different data categories, and there is always the possibility that a
user will want choices not yet there that fit a different category.
The question I have is how to list all these headings in different
columns based on their data category. The data category for each
individual heading would be contained in a comment in that cell.

The complete composite header row stretches from A1 to AD1 at the
moment; more will likely be added later. I'd like to go to CA1, put
the data category name, and write all headers for that category in CA2
and down; go to CC1, put the next category name, all headers for that
category in CC2 and down; lather, rinse, repeat for all categories.
All this would be in a Workbook_Open sub.

My general fuzzy idea goes something like this:
-- Iterate through the cells of the whole header row.
-- Read the comment text of each cell to get the category.
-- Write the text of the header cell in the column for the proper
category.

The tough part is not knowing how many categories there are or what
their names are, because things will get added as we go along. That
means I have to track which categories have been created; if this next
one has not been created yet, then go to the next column over and
create it, then write the header under it. (The user will then scan
these lists, put an X by the desired headers, and click a button to
build the desired header row n a new worksheet. That part I think I
can handle.)

Do I create an array variable on the fly for each category, and then
iterate through all my arrays to write each one to the worksheet? Is
there a better way to track "categories created" so I know when I hit
a new one? How would y'all approach this?

Ed
(Working in XL2003)