Need help creating macro to move cells from same row to diff. Sheet based on value of cell

You have two columns with Sales Rep, one is Buyer, the other is Seller. Which column do you want to use as a basis to create a new sheet for each rep?

column AJ
please check my post, i updated it with the columns i would like copied
thank you!

Hello lmh79. I think the following code will work for you. There is only one little problem I can't seem to figure out. I think my brain is a little frazzled! You have to run the macro twice. The first time it creates the sheets and the second time it tranfers the rows. Try it out and see what you think. I'll keep working on the problem and maybe in the meantime, there is someone else in the forum who can figure out why you have to run the macro twice.

Please Login or Register  to view this content.

Try the attached

thank you both for your replies! yet again, i am new to this macro version of excel.. is there any way someone can tell me how to apply either of these helpful tips to my master spreadsheet? i am kind of intimidated by this new frontier..

What do you mean by master spreadsheet? My code creates tabs(sheets) for each sales person based on column AJ. As you can from the attached, the code has created tabs for each person. You can amend sheet 1, or master sheet, as many times as you wish and re-run the code, it will populate the tabs with new data, or if you add a new sales person, it will create a new tab with that persons data.

ok. so i just copy my spreadsheet into sheet one of the workbook you created and it will give me the data i need. my other question is: how do i alter the sales reps. sheets to only include these columns?
A:D, I, M:N, Q, AC:AF, AI:AK

Hello AB33. I believe that the following two macros will do what lmh79 wants but I was hoping you might be able to explain to me (just to add to my learning of VBA) why the two macros have to be run separately. When I try to combine them, the sheets are created but the data is not copied over to each sheet. When run separately, they work. Many thanks.

Please Login or Register  to view this content.

Okay, Mumps 1, give me few minutes to look at your code

Mumps 1,
I think the first of your code works, that it creates sheet names, but the second part does not copy the ranges. I just combined both codes by calling the second from the first one.

Please Login or Register  to view this content.

Thank you, AB33. I found the problem. After the last sheet was created, it remained, of course, as the active sheet. When the section of code that copies over the data runs, it applies to the active sheet instead of Sheet1. I added one line to make Sheet1 the active sheet and then copy the data and this worked.

Please Login or Register  to view this content.

Mumps 1,
Great!
You probably need to add to your code Application screen updating to speed up the code.
You also need to include the heading for each sheet.

thank you both so much for your help! i copied and pasted my spreadsheet into the workbook that AB33 sent to me and it looks like all i need to do is format the sheets with the SR names.

i have one more favor to ask: is there any way you can tweak the code you wrote to include column AI along with AJ-- so that if the sales rep name appears in either column, the data in that row will show up in that sales rep sheet.

thank you!

I just want to clarify what you are asking. To use and example from your sheet .... in row 4, Mike appears in Column AI and Clay T appears in column AJ. Do you want row 4 to be copied to both Mike's and Clay's sheets?

yes, please.

Please Login or Register  to view this content.

lmh,
I spent a couple of hours on this thread as you keep changing your request. Having read Mumps' reply, my last code does not duplicate names. For e,g in row 5 you have Mike in both columns, my code will create a single mike's sheet name. In other words, if the name appears in both columns, it will only copy one name, but if the name does not appear in column AJ, but in AI, it will create a new name as this name does not exist in both columns.

Please try my code and clarifay what is missing

lmh,
I spent a couple of hours on this thread as you keep changing your request. Having read Mumps' reply, my last code does not duplicate names. For e,g in row 5 you have Mike in both columns, my code will create a single mike's sheet name. In other words, if the name appears in both columns, it will only copy one name, but if the name does not appear in column AJ, but in AI, it will create a new name as this name does not exist in either column.

Please try my code and clarifay what is missing

@AB33 Thank you so much for your suggestions.

@lmh. Please have a look at this attachment. See if it does what you wanted.