Split into multiple files using combo box and button

Originally Posted by arlu1201

I am not seeing any values in your dropdowns for both options.

Also, how are you ensuring that the user doesnt click both option boxes?

When you say the formatting for sheets A & B should be retained? Do you mean the formatting in the new files that are created?

In what format should the new file be? Should it have the filtered data for each respective sheet in its own sheet? Where should the file be saved and in what name?

Hi Sir,
I have updated the file pls ignore and delete the other one.
the formatting of the sheets a and b should also be retained in the new file.
the new file will be in excel 2003 format
ill take care of the filename, the new file should contain sheet a and b only, instructions sheet not included.

Originally Posted by Steffen Thomsen

Hi,

Just had a short look at it, it's a project all right, i will not do it all, but will instead point you in the right direction.

You populate a combobox like this

Please Login or Register  to view this content.

If the combobox should hold a lot of values then create an array and asign the values to the combobox list

Next you should look in to the "Combobox_change" event

Please Login or Register  to view this content.

next up you create a new workbook, you can read how here

http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

Next you copy the data, here you should look into specialcells to copy only visible part of sheets 2 and 3

Please Login or Register  to view this content.

After coppying you save the new workbook, this you can read about in the link given earlier or in more detail here:

http://www.rondebruin.nl/saveas.htm

Clear the filter and your done!

If you have en specific questions along the way feel free to ask them here

thank you sir, but i am still new in macros, should i paste the code on the buttons and combo boxes?
the option 1 drop down will contain 5 options and the other one will contain 3 options only, so i will have 8 files to generate.

Originally Posted by Steffen Thomsen

Should all 8 file be generated every time? If this is the case i think you going about it the wrong way.

The code for the populating the comboboxes should only be run once if they never change and therfore be placed in a module and then deleted.

The combobox change event should be placed in the first sheets codemodule, so should the "CommandButton1_click" event

Hi sir, 8 files can be generated everytime because we need the reports regularly, however i should also have an option to generate one by one.

apologies for the confusion, the objects that i created are form controls, it should be activex

I appreaciate your help guys, ill be waiting for your responses.