Need code to copy data based on filter criteria to new workbook as save in a file location

Hello,

I have a data set with 4 columns. I need to copy all data for each sales rep to a new workbook and save the file in a network location. I have the code to do this for one rep, but my actual file has about 50 reps. I'm having trouble looping through the filter criteria and creating new workbook names for each of the reps. Here is the code I have so far:

I have also attached the file. Any help is greatly appreciated. Thanks.

Please Login or Register  to view this content.

Perhaps something like this? I've added an extra sheet where you add the name of the sales representavives.

Macro checks if a sheet with the name excists if so filterd data is added to excisting data. If sheet don't excists macro will create it and
add the data.

To test run macro "ExtrData"

Alf

Hi Alf,

Thanks a lot, that looks great. However I now just need to save each of those tabs separately in a different workbook in a a file location. Can that be added?

Can that be added?

Yes that is not so difficult but perhaps we first should sort out how this should be done.

I would sugges that everey time the macro is run all sheets except "Data" and "List_rep" are deleted starting with a clean "slate" so to say.

After a sheet is created this sheet is also saved as a xlsx file. The name of the file? Perhaps rep name + date i.e. "john_02/20/2015.xlsx". The advantage of this is that you can have a number of "john" files in the same folder.

What kind of folder structure do you wish to use? "\\Network\MyFolder\all-sales-rep\" or do you wish to save all "john" files in a "john" subfolder, all "bob" files in a "bob" sub folder and so forth?

Another alternative setup is that the maco just created all the sheets so you can easily check if data in folders look ok. Then if everything is ok you run the second macro "Create_Files" that "converts" the different worksheets into files.

Tell me what you prefer I'll try to build it.

Alf

I agree that the sheets should be deleted every time the macro is run.

Their only needs to be one folder to save all of the files in. As for the file names, they can just be the sales rep name + the date.

I would prefer that everything is created and saved as soon as the macro is run, but if that is causing trouble then breaking it up into two steps would not be an issue.

Once again, thank you very much for the assistance.

Ok here comes the updated code:

Macro first delates all sheets except "List_rep" and "Data"

Then new sheets are added based on the names in "List_rep", macro then loops through all sheets and creates files of sheets not named "List_rep" and "Data".

You need to change the line

Please Login or Register  to view this content.

i.e. the "I:\btest\" part with the part that points to your network location.

Macro now looks like this:

Please Login or Register  to view this content.

Alf

Alf,

This works wonders. I am very grateful. One quick question for you if you don't mind - can you help me understand what "cell.value" is doing? I don't see how Selection.AutoFilter Field:=1, Criteria1:=cell.Value, Operator:=xlAnd is catching all of the names in the Rep_name list.

Thanks again for the help

There is a loop going through the range "Rep_name".
The loop starts with "For Each cell ln Range. ...."
Then comes a number of instructions what to do
The "Next" line says "ok you have done everything I asked for, now take the next cell in the specified range and do the same things again.
The first cell in Rep_name" is A2 and the value is "bob", the second is A3 with "john" and the last is A4 containing "steve"

Hope this makes it easier to understand, if not ask again.

Alf

Got it. Thanks for the explanation. Once again, the help is much appreciated!

You are welcome. Thanks for feedback and rep

Alf

Ps don't forget to mark your thread "Solved"

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved