Macro for searching on sheet 1 with data from sheet 2 and create new sheets

Ok here is my issue, I get excel files listing of clients that need to be updated. We have assigned reps for each client. So what I have created was a workbook with a list of our reps with their client ids example:

Sheet1
client lists for updates/changes

Sheet2
REP | REP | REP | REP
id# | id# | id# | id#
id# | id# | id# | id#

Sheet2 has a list from A-I

What I want is if any data in sheet1 matches the list for Rep A it will copy the entire row(s) from sheet1 and create a new sheet for just that rep on the new sheet. Same for rep B,C,Etc. so on.

Can you attach a file with some sample data? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

And a note, I know nothing about vba or creating macros.

I've attached a sample

Try it on your sample file too see if it does what you want.

Originally Posted by JOHN H. DAVIS

Try it on your sample file too see if it does what you want.

That works great if the client IDs are always in row B, but what if for example I get a sheet that has the client IDs listed in another row like A, C, D, etc.?

On another note, my lists generally are at least 200 rows long up to over 1000. I noticed this only scanned the first few lines on the sample, what would i need to change to use this on my actual list?

Originally Posted by stitchoz

That works great if the client IDs are always in row B, but what if for example I get a sheet that has the client IDs listed in another row like A, C, D, etc.?

It searches Sheet2 regardless of which Column? It searches Sheet1 Column B. There has to be someway to tells what is a client ID and what is not if Sheet1 Column B is not the Column which has the IDs.

Originally Posted by stitchoz

On another note, my lists generally are at least 200 rows long up to over 1000. I noticed this only scanned the first few lines on the sample, what would i need to change to use this on my actual list?

It searchs from row2 to the last row with data, so that shouldn't be a problem.

Originally Posted by JOHN H. DAVIS

It searches Sheet2 regardless of which Column? It searches Sheet1 Column B. There has to be someway to tells what is a client ID and what is not if Sheet1 Column B is not the Column which has the IDs.

Ok, so if for example the client ID is in column C, instead of B. Where in the macro would I change that information?

Originally Posted by JOHN H. DAVIS

It searches Sheet2 regardless of which Column? It searches Sheet1 Column B. There has to be someway to tells what is a client ID and what is not if Sheet1 Column B is not the Column which has the IDs.

Would this work?

Please Login or Register  to view this content.

Should work.

Modified:

Please Login or Register  to view this content.

That worked perfectly! But I do have one thing, even though it worked perfect I did get a vb error "Run-time error '91' Object variable or With block variable not set" Should I just ignore and hit end when I run this macro or is there way so the error doesn't pop up. Other than that, it worked exactly how I need it to, I really do appreciate the amount of time you took out for this to help me!

I can't find anything that would cause that error. And if it did then the macro should have stopped. Can you post your code exactly as you have it?

Try:

Please Login or Register  to view this content.

I copied the code per your modified, here is the file I'm working with. You can ignore the unnamed sheets unless there is a way to where it only creates for just the reps I have listed in sheet 2. Tx rate changes.xlsm

code:

Please Login or Register  to view this content.

This is the original without the macrotx client list.xlsx

The code you post in Post19 is not the modified version.

Try this:

Please Login or Register  to view this content.

Is this it?

Please Login or Register  to view this content.

Still same issue, it works but gives the error in the same area of the code. Do you think it may be because of the unknown client IDs? Maybe if somewhere it could only pull what is on the sheet2 list, and for what is on sheet1 that isn't on sheet2 to ignore those values?

Originally Posted by stitchoz

Still same issue, it works but gives the error in the same area of the code. Do you think it may be because of the unknown client IDs? Maybe if somewhere it could only pull what is on the sheet2 list, and for what is on sheet1 that isn't on sheet2 to ignore those values?

I'm sorry, but I don't receive any error from the code I posted in Post#24? I used in on the last example you provided. No issues at all?

I have edited the Rep List to be all listed in columns A with the name and B with the ID on sheet 2. Maybe with it setup this way it will not occur with the error. Here is a copy without any macros on it, possible to see if this works with the macros?

Originally Posted by JOHN H. DAVIS

I'm sorry, but I don't receive any error from the code I posted in Post#24? I used in on the last example you provided. No issues at all?

The sample sheet doesn't generate any errors, its when I use it on the full scale one that it does.

Scratch that, it did work, I'm not sure what I did wrong the first time.

Originally Posted by stitchoz

The sample sheet doesn't generate any errors, its when I use it on the full scale one that it does.

I wouldn't be able to tell? I'm afraid I can't offer much else.

Originally Posted by stitchoz

Scratch that, it did work, I'm not sure what I did wrong the first time.

If it is working, please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.