Copy Row/Rename based on Cell Value

Hello Forum Gurus!

Need help with some VBA or macro to basically copy the content of a cell and rename with the addition of the value of other cells (X in Category field). Each row will have AT LEAST 1 X, but will not likely have ALL categories marked X.

Ex. CategoryA CategoryB
cell name1 X
cell name2 X X

Desired results:

cell name1_CategoryA
cell name2_CategoryA
cell name2_CategoryB


Thanks for any/all help!
Betsy

Just to clarify these X's will be names or text strings.

Or will they be literally X's and you want the heading to be added

Its acting as a check box basically saying that this cell name does in fact have this category

Also where would you like these news names placed

Just after the original name separated by an underscore if that's possible.

THANKS!

You got it!

So for example if cell a2 is the original name then all of the cells to the right are the categories you would want the name to be replaced by the new name?

And if for example you had 2 x's then it would be Originalcellname_x1_x2?

Sort of. Each new name needs to be it's own row so that they can be sorted etc. So it should look something like this if the original name has an X in two categories:

original name_newcategoryA
original name_newcategoryB

Obviously if there is only 1 category with an X then that row would only be copied/renamed once with the original name+new category.

Can I generate a list on a new sheet of all of the new names?

Or does it require that it is on the same sheet? (if so can you post a small sample that I can look at with the start and end goal so I can see exactly what you want)

New sheet is fine. I did upload an example though...guess I attached it to one of the messages above...can you see it?

Based on what I know this will check the cells assuming the headers (categories) are in row 1 and the names are in column A. let me know if more help is needed

It also puts the new names on sheet 2

Please Login or Register  to view this content.

I get a subscript out of range error. Not sure why. The rows should contain the original cell name in A2 and the columns are the various categories in B1, and the X are in B2, etc....but I think I can switch the code around...if I can get it to work the way you have it. But not sure what to do about that error.

Most likely the issue is the worksheets name if I had to guess. Where it has sheet2 you will need to change that to the desired worksheet name for where the names will be stored.

Also just to make this more dynamic and not run 10000 times, is there always the same amount of rows and columns?

No, there won't always be the same number of rows because some will have X's in ALL categories while others won't and that will change every month.

Side note...it is aggregating ALL the new names onto the original name with an underscore for each category...but they need to each be in their own rows...not all in the same name.

So if a record has 2 x's, it should have 2 corresponding rows....not one with two names. So it's not really "total cell names combined" but rather cell names combined for EACH
original name + new name....then a new row for the next.

Are you able to fix that?

This is GREAT! Correct.....there will never be a blank in row 1. As far as columns, there won't be a blank in B, but every month the number of columns will change so one month there could be only 3 columns while the next month there could be 10. I'm not sure that qualifies as "blanks" though. So yeah, running as many times as there are rows and columns would be great since that could ensure I don't miss any data.

I SUPER appreciate you and all that you do on these forums. For newbies like me, it just makes such a difference and I am very thankful

Betsy

I changed the code to make run the least amount of times possible. (Do not if there is a blank cell in row A in the middle of the data it will stop running. For example if you don't have a NF000x in the name column. The code finds that blank to be the end)

I added comments on what is different from what I have sent before.

Please Login or Register  to view this content.

Also if this solves the thread please mark it as solved at the top under thread tools and feel free to add reputation if you feel I have been helpful

If you need more help let me know.

YAY! This is perfect! I'll try it out tomorrow with some real data and I'll let you know if I have any problems. I'll mark it SOLVED though...since you DID

THANKS!