Automatically create subsheets based on master sheet category type

Hi,

I am new to the forum and hope I am posting this in the correct forum. I have searched through several sites and threads that seemed similar to what I am looking for but they aren't quite what I am looking for. Any help is greatly appreciated!

I am creating a master equipment list for the company I work at. I would like to have one master sheet that gets updated each time new equipment is purchased. From there, worksheets are created based on the category of the item purchased. I would like to be able to update information, add, remove equipment (ie, if it breaks or is sold) and have that information automatically update in the subsheets.

For example, the master sheet would have the following categories of equipment:

Hard Drives
Software
Monitors
Computers
Speakers

From there, I'd like to have individual sheets for each of the above categories with all of information in the items row added those individual sheets.

I've attached an example of what I'd like my excel sheet to look like.

From my searching I'm starting to get confused with what I should use to accomplish this (VLookup, Pivot table, Macros, Visual Basic, etc). I tried advanced filtering but that doesn't seem to automatically update as changes are made to the master equipment sheet.

Thanks for any help you can provide!
Adam

Hi

you could have events on each of the output sheets that brings in the data via advanced filter each time they are selected.

Right click on the Hard Drive tab, select view code, and put in

Please Login or Register  to view this content.

Now whenever you select the hard drive sheet, it will automatically update.

You will have to modify the code for each sheet to nominate the data to be selected.

HTH

rylo

Hi

Or if you make sure your sheet name is the same as the equipment type you could have the same code on each sheet.

Please Login or Register  to view this content.

rylo

A slightly different setup as compared to rylos

Run macro "CreateSubS" to test. Each unique item in the "Equipment" sheet will be added as a separate sheet.

When doing a rerun all sheets except "Equipment" sheet is first deleted and then sheets are added based on unique items in "Equipment" sheet.

Alf

Hi,

Wow. Thanks to you both, Rylo and Alf! Really appreciate the time you took to help me out. I would not have been able to figure either approach out on my own.

Rylo, this works pretty much as I describe. Thanks! My only concern would be if the category sheets (ie, Hard Drive) gets updated it would then get deleted when "Equipment" is updated. Is there a way to execute the excel sheet as is but have it so any changes made on sub sheets (Hard Drive, Monitor) are reflected back on the equipment page? If not, no worries. This will be a shared document, so I'll leave a note warning people of that fact. Also, I liked the solution to match sheet name with equipment name.

Alf, thanks for taking the time to put this together. Looks like a fair bit of code! A couple of questions:

After going to Tools > Macro > Macros > Run ("CreateSubS") - should I be sure to select "Marcos in: This Workbook" or Macros in: Equipment_Example_v2.xlsm?

When I do run the "CreateSubS" macro I get a message:

Run-Time error '1004': Method 'AutoFilter' of object 'Range' failed.

When I select "continue" the Macro runs but takes the information on the "Equipment" sheet and moves it to the sub categories (Hard Drive, Software, etc). Is there a way to keep all of the information on the equipment sheet after the macro has been run?

Thanks again!
Adam

Adam

The way I interpreted your original post was that all data would only be entered, deleted, updated on the master list and then distributed to the individual lists. There doesn't seem to be any mention of an update to a sub list going back to the original.

If you keep the information flow one way, then things can be more easily maintained. To help this along, you could have the sub sheets locked, so that no data on that sheet can be modified / added etc. The unlocking / updating / locking could all be handled by the code.

rylo

As an option, but I do not know how it looks like in Mac.

When I do run the "CreateSubS" macro I get a message:

Run-Time error '1004': Method 'AutoFilter' of object 'Range' failed.

I think I missed the info in your sig i.e. "Excel for Mac 2011" so I don't really know what to do. This macro works without problens in Excel 2007 and 2010 but it seems it will not work in the Mac version.

Alf