Extract from multiple worksheets based on one condition and output to new workbook

Hello,

I am trying to create a button that generates 3 reports based on one condition. Please see the attached file.

There are 3 tables to be generated, namely, worksheet A, B and C.

In worksheet A, there is a master button, "Generate quarter report". I would like to first select a specific quarter(Q 1, Q 2, Q 3 or Q 4) from the dropdown list in cell D2, and hit that master button. The purpose of the button is to filter all of the records from three worksheets based on that quarter selection and output the 3 worksheet recordsets to a new workbook.

Additionally, I would like the new workbook to be able to prevent users from modifying records in the output files by default. (simply just want to have columns locked)

Is this possible?

Thank you

Originally Posted by arlu1201

Your request is not very clear. All i see in tabs A B and C is Q1, Q2, etc & P1, P2 etc. There is no data available.

Oops sorry.

Please see the attached with more fields.

So basically, when the button is clicked, I would like to have Excel filter on all worksheets based on either the period selection OR the quarter selection, depending on what user's needs are, and then output the filtered recordsets to a new workbook.

The generated files in the new workbook just need to contain all 4 original columns, so data in this context means everything you see from Col A to Col D.

Please see the attached file

Thanks

Hi, could you respond to my subsequent post when you get a chance? Is it possible?

Thank you

Originally Posted by arlu1201

Hey Lifeseeker,

I guess many were away for the christmas vacations so your post remain un-answered. Do you still need a resolution?

Hi,

Yes please. Happy New Year first!

What I would like:

To have a button that when clicked, filters all records based specified quarter and export the filtered recordset to a new workbook with same properties. (for example, if columns are locked, i want the exported table to have the same property)

Is this clear?

Thank you

Originally Posted by arlu1201

Just one last question - I understand that there will be 3 sheets with data- A, B and C which needs to be filtered. Should the records be exported to 3 different worksheets of the new workbook? Or should it be exported to the same worksheet of the new workbook?

Hi,

Actually....would it be possible to export to 3 different workbooks where each new workbook just contains one table? So workbookA will store A, new workbookB will store B, etc.

If this is not possible, then 3 different worksheets of the new workbook please.

Thank you

A please, different workbooks.

Thank you

Hi,

Thank you for doing this.

I am getting an error: "subscript out of range" on

Please Login or Register  to view this content.

i thought I didn't have to activate this workbook for it to recognize?

i am also attaching the file.

Ah I see. I'm sorry for not being 100% clear...

Would it be possible if all 3 recordsets are in 1 worksheet as the way it is(this file), but when I do the filter and export, I would like to have 3 recordsets( A B and C) be in 3 different workbooks as opposed to 3 different worksheets?

As you can see, all recordsets have exactly the same format, which would be easy.

So after the export is done, that recordset table A will be in workbook A, table B will be in workbook B, etc. I want like to keep them separate please.

Please see the attached.

Is anyone able to assist?

Hi Arlu,

Are you able to help with the request?

Thanks

Hi Arlu,

few issues.

When I run the code, it says bk1 already exists in the location. Do you want to replace it?

And if I hit "yes", then I get "subscript out of range" error and code

Please Login or Register  to view this content.

is highlighted.

If I hit "No", I got workbook saveas method failed and code highlighted is:

Please Login or Register  to view this content.

please see the attached.

Any ideas?

Hi Arlu,

Are you able to help?

Thanks

Is anyone able to assist?

Hi Arlu,

I still run into errors on this one..

Would you mind checking the code in the file?

You get no errors at all?

I keep getting the subscript out of range error this time after I click on the button. Error line is on

Please Login or Register  to view this content.

Thank you for your time.

Please Login or Register  to view this content.

Is anyone able to assist please?

Thanks

Hi Arlu,

hmm this time I do see three workbooks being opened.

However, there are no values contained in the new workbook.

For example, I picked Q3 in the "Master", and after Excel opened 3 workbooks and I went in and see, I would have expected that all the Q3 rows appear in each of the workbook, but only the header column is there.

Do you get the same thing on your end or?

Something is not working out right

Hi Arlu,

Yes, that would be great if I could also have the record locked on the exported recordset.

thank you!

A millions of thanks is not enough to express my gratitude.

A millions of thanks is simply not enough to express my gratitude.