Automatically bring new sheet's data into master sheet

[mattmitchell0203]

Hello all,

I am trying to figure out how I can bring specific data from all sheets in my workbook to a master sheet. The workbook has data from a "problem list" with items we are trying to fix in my workplace. The column headings are:

Complete?(Y/N/IP); Area; Observation; Root Cause; Proposed Solutions; Urgent? (Y/N); Owner

I am trying to make it so that all of the rows marked "IP" (in progress) or "N" (not complete) under the "complete" column can auto populate into a master sheet so we can keep track of outstanding issues that haven't been resolved or worked on yet. There are a varying number of rows in each sheet. I'm guessing a macro would be needed for this, and ideally it would be able to update the master sheet automatically, even when new sheets are added -- a sheet is added for every week of issue collection.

Thanks so much, anything helps!

[Ochimus]

Try the following.

It cycles through every worksheet not called "MASTER", filters the "IP"s and "N"s and copies them to the next free row in the Master sheet, clears the filters and repeats for the next worksheet.

Please Login or Register  to view this content.

Ochimus

[vcoolio]

Hello Matt,

Here's another option. Not tested though:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

[mattmitchell0203]

Thank you both for your suggestions, but unfortunately neither of them worked. The first code seems to be incomplete and the second didn't quite pull the data that I need. Any updates/ideas?

[vcoolio]

Hello Matt,

and the second didn't quite pull the data that I need. Any updates/ideas?

Could you elaborate a little more please. Is there something more that you need to tell us?
Based on your first post, you have seven columns (A - G) and whichever row has the criteria "IP" or "N" in Column A then that row (from A - G) needs to be transferred to the "Master" sheet. The following line of code:-

Please Login or Register  to view this content.

does that.

Do you have more columns to deal with?

It would be best if you attached a sample of your work book so that we can see exactly what it is that you would like to do. Be careful with any sensitive data.

Cheerio,
vcoolio.

[mattmitchell0203]

Thanks, vcoolio, for being so helpful. I think I may understand why the macro didnt work, and I think you'll see after looking at the attached pictures, too. I forgot to mention that the sheets I'm trying to pull data from have other information in the first 11 rows.

template of gemba tracker.PNG

template of gemba outstanding issues.PNG

I've attached 2 images. The first is the template we use to insert each week's new data. This is where the data should be pulled from and inserted into the master sheet.

The second image is the master sheet itself. I only included a few rows merely for privacy reasons.

So, as a brief summary, every week we put new data into the first image's template, and we want any rows that have either IP or N in the progress column to be automatically transferred into the master sheet.

Hopefully that helps, and thank you so much for your help! I'm terrible with VBA and appreciate your assistance.

[vcoolio]

Hello Matt,

For whatever reason, the images that you posted don't light up.

Could you please attach a de-sensitised sample of your work book.

Cheerio,
vcoolio.

[Ochimus]

Matt,

Filters everything on Input sheet from Row 13 to last row that is "N" or "IP", moves the filtered rows (excluding header) to Master sheet, clears filters.

Please Login or Register  to view this content.

You didn't say what you do with the "Y" rows left behind on the Input sheet, so the Code doesn't do anything with them

[vcoolio]

Hello Matt,

As I still can't access the images, I'll base my solution on the sample file that Ochimus has supplied. So, the following may help:-

Please Login or Register  to view this content.

Cheerio,
vcoolio.

[vcoolio]

Hello Matt,

As I still can't access the images, I'll base my solution on the sample file that Ochimus has supplied. So, the following may help:-

Please Login or Register  to view this content.

Cheerio,
vcoolio.

[vcoolio]

.............and I don't know why it posted twice!!

[mattmitchell0203]

Thanks guys, for all your help.

Ochimus, yours worked all except for that it cut the data from the input pages rather than copied them. Also, it only applied to the one worksheet rather than all worksheets other than the master. Sorry I didnt get a file uploaded before, I didn't have access to my work machine. Here's a desensitized version which I hope will make it easier to get what I really need.

Thank you both, again for your help; I've never met strangers who are so nice and willing to help before

[Ochimus]

Matt,

Not sure why I left the "cycle through them" out of the last post - it was in my original!

Attached does what you need,

Code goes through each worksheet, and if it is NOT called either "Master_ALL ISSUES" or "Template", it copies (not cuts) the "N" and "IP" rows to the Master.

Please Login or Register  to view this content.

Ochimus

[vcoolio]

Hello Matt,

It looks like Ochimus has it covered for you. Here's my two cents worth anyway (again, untested):-

Please Login or Register  to view this content.

Cheerio,
vcoolio.

[mattmitchell0203]

Thanks, guys.

I got it up and running and it works great. My boss will be very happy about it.

A couple more questions:
1. Is there a way to make it so that every time I add a new week's worth of data on another sheet it will add the data automatically?
2. Is there a way to "link" the data from the input sheets to the master so that if the input sheets are modified, the master will reflect the changes?


Thanks so much.

Matt

[mattmitchell0203]

Also, when I added in another sheet's data and re-ran the module, it duplicated all of the information that was copied to the master the first time i ran it. Is there a way to make it so that the code automatically a) updates when a new sheet was added b) doesnt copy duplicate information and c) automatically updates when the information is changed on the input sheets?

Thanks a bunch, sorry I'm so difficult with this.

[Ochimus]

Matt,

Sorry, our posts must have crossed!

The Code you have is a "one off" approach. When you run it on "Day 1", it will work through every worksheet and copy the filtered data.

If you then add a new week's data on a fresh sheet, it will copy those new records automatically,

The problem - as you have found - is it will ALSO re-copy the other sheets, so assuming the same record on a sheet is "N" on both occassions, you will now have it on the Master file twice, then three times the next time and so on.

Unless you are "doing something" elsewhere on the Master sheet, the obvious solution is to amend the Macro, so it starts by deleting everything on the Master sheet from Row 12 downwards, and then copies all the relevant records back. Which would also ensure any "modifications" on the Input sheets will be reflected on the Master every time you ran the Macro.

Ochimus

[Ochimus]

Add this row to the first part of the Code I sent you - it should clear the rows beforse starting the search

Please Login or Register  to view this content.

Ochimus

[vcoolio]

Hello Matt,

Ochimus now has it covered for you but I had that covered in the code that I supplied in my post #14.

You can add as many sheets as you like. The code will cover any additional sheets.
The Master sheet will be "refreshed" each time that you run the code with data from both the older sheets and the new sheets without duplication.

Cheerio,
vcoolio.

[mattmitchell0203]

Thank you guys,

I've got it to work and I added in a part to make it automatically run on it's own.

It works great, thanks again for your help; not very experienced with VBA.

Best,

Matt

[Ochimus]

Matt,

If we've solved everything, can you just go to "Thread tools" at the top of the page and click this as "Solved".

And if you're feeling generous, vcoolio deserves a click on "Add reputation", as his code was more elegant than mine!

Ochimus

[vcoolio]

Hello Matt,

You're welcome. Glad that we were able to help. For the sake of any future visitors to this thread, please post your little addition to the code. That way, they can see the completed product.

@Ochimus:

Thanks for your kind acknowledgement. You did the real work on this thread. I just chipped in a little.

Cheerio people.
vcoolio.