Splitting data based on column value

Good morning,

I work for a manufacturing company, and I've been trying to create a new spreadsheet that will split data of Non-conforming material based on a supplier number into a new sheet. I would also like each new sheet to be in the form of a table. My goal would be to link each table to one slicer that sorts data based on week ending dates. From there, I can email each supplier their own list of non-conforming material for the week.

I realize that I can accomplish some of this by "Show report filter pages..." pivot table function, but the problem is, this database is refreshed daily, and when a new supplier that hasn't been in our database before shows up, the pivot tables have already been created and there won't be a new page with a new pivot table for the new supplier (My data source for the pivot tables goes the entire length of the columns).

I have also tried parsing data using the code below, but I find that it only runs effectively one time initially. So if I try to run the macro again, it won't split new data. Also, this code below doesn't do anything with formatting the data/keeping data in table form.

Please Login or Register  to view this content.

My columns range from A-S, my columns with the supplier number I want data split by is in column N (VCOL = 14), and my week ending dates are in column Q (VCOL = 17)

I'm fairly inexperienced with VBA, but I've been resourceful in accomplishing smaller tasks. I'm a bit out of my depth on this apparently.

Thanks for help!

It will take a bit of VBA. You can create a second table with vendor names only and then check to see if a sheet for the vendors exist. If not copy a "template" sheet and name it for the vendor and set the filter. I'll have to see the format of your source data and a sample of what you want the end result to look like.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

In between posting and your response, I found that I can't actually link tables using a slicer unless they are on the same worksheet, which is unfortunate. So I guess what I want to accomplish is to have my main data source, which is a connection of another spreadsheet and will be refreshed once a day, to be split into seperate worksheets by vendor number. If possible, I would it to be in pivot table form, that way I can link all the seperate pivot tables with a slicer and filter them by week ending date.

As I mentioned initially, the show report filter pages function which can be found in pivot table options will accomplish most of this, but once the master pivot table is separated, new pivot tables won't be formed when there is a new vendor that appears in the main data table that wasn't in the data when the first set of pivot tables were created.

In the file called "Sample of Data", I left 20 lines worth of data but I also left the Macro I had been using as well. When it's run, I can't run it again. At the very least, I would like to have it so that I can re-run the macro when data is refreshed.

In the file called "Pivot Table sample", I took the same file and ran the "show report filter pages" function to demonstrate what I'm trying to accomplish.

Thank you for bearing with me as I explain myself as I'm sure I'm leaving something out. I do appreciate your feedback.

Edit: I did not link the pivot tables together with the slicer as I had mentioned in the Pivot Table Sample file.

I think this may be what you want. I kept your data: you can import / copy / paste or whatever to get data in there.

I kept your Master Pivot Table. I created a pivot table with just the supplier name in the Suppliers Tab. This is the unique list of suppliers in the database.

I created a slicer for the master pivot table.

I did a one-time remove of all the supplier tabs and ran the program. The program looks at the supplier list and if the sheet does not exist, it copies the master pivot table sheet to the end of the workbook, changes the name to the supplier name, puts the supplier name in cell B1 (the filter) and then creates a slicer on that page. ALL the slicers are linked together. Change one and all of them change.

P.S. From here on out, you can keep the existing supplier pages. If a new supplier shows up, only that supplier will be added to the workbook.