Combining multiple tables into one without changing the data

Hi Everyone,

I was wondering if anyone can help guide me in the right direction, I think this should be easy but for the life of me I can't figure it out.

I have a workbook full of sheets that have identical tables in them, each sheet for a different project. Each project uses different chemicals and they all have different expiry dates. What I would like to do is see all of the expiry dates in one big list so I don't have to go to each sheet separately to see when my standards are going to expire, I would like it all listed in one sheet that I can sort as I please. Here is a snip of the type of table that I am talking about, I just need the first 5 columns.

Attachment 643896
Capture.JPG

So I basically just need to take col A to E all rows with something in them minus the top header and append them into one big table and have the ability to update this table either when loading the excel workbook or clicking a macro button (There are 32 tables in total so I would rather use something optional so it doesn't take long to load the book when you're just updating something)

99% of the Standards in all of the sheets are named differently however there are a few that are the same, I do not want these merged but listed separately.

I have Excel 2013 at work so can't change the version but I can install add-ons (from MS only).

Thank you for any help

.

Please Login or Register  to view this content.

Hi Logit, thank you for your reply and sorry for my late response I finally got time today at work to try out your code and it worked great thanks. I attached the macro to a button and I was wondering if there is a string that we can add that will clear the sheet of the previous search before it populates for the current search?

Ahh thank you so much, you have made my job a ton easier and I learned a lot. I forgot to mention to clear contents but keep the header row so I changed the code a little to this WS1.Cells.Rows("2:" & Rows.Count).ClearContents - not sure if that is the right way to do it but it works lol

Again thank you so much, I really appreciate you taking the time to help a noob out.

Hi, me again

I have been playing around with it for the past day or two and I almost have it exactly the way that we need it, just one more thing that I can't figure out I keep getting an error message when I try.

Right now I am coping a2:f but I need to add H in there as well, skipping G. Would you be able to help me out with that?

Thanks a lot!

Originally Posted by Logit

.
You got ! Glad this will make your job easier.

Happy to help.

Cheers !

.

Please Login or Register  to view this content.

Thanks for your reply, however that code doesn't skip col G which I need to omit as it has check boxes in it that wont sort or clear with the macro.

Originally Posted by Logit

.

Please Login or Register  to view this content.

.
This is one method :

Please Login or Register  to view this content.

I tried something like that but it wont delete the check boxes that are in G and I need to keep the header row as well, this is what it gives me after it "deletes" col G

Attachment 644787

.
I couldn't view your attachment. The Forum Server says it is not a valid format.

In any case ... see if this does it :

Please Login or Register  to view this content.

Ahhh I didn't think of just duplicating that line again, I kept trying to add code to the existing one. That worked perfectly thank you again