VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide

  • Hello,

    I have a bunch of worksheets for different "spaces" in a house. I then have several sheets that are for a "system" to accomodate all those spaces. I have a form that I can fill out all kinds of information on each space and it will sum it up.

    What i'd like to now is create a sort of "finish" button, that when hit will take information from certain cells on each sheet, and automatically place it on the "system" sheet, along with the name of the space on the first column, and then hide the "space" sheets and only show each "system" sheet. I then want to have it so I can click on the name of the space on the "system" sheet and have it unhide the corresponding "space" sheet.

    I'm having problems with having it hide each sheet, and know which sheets are system sheets and which are space sheets. I also don't know how to make it so when I populate the system list with the name of the space sheet, that it will allow me to click on that name, unhide the space sheet, and go to it.

    Any help or recommendations?

    I can attach the sheets if it would make it easier

  • Re: VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide


    I'm going to give you a start here, some examples.

    Here's a code that loops through all your sheets and hides all sheets where the name begins with "space". It also shows the use of a messagebox, displays sheetname.

    To select a sheet:


    To recognise if your sheet is a system or space I would you use the sheet name like "system_Basement" and "space_Basement" or you have to put a name on the sheet itself in a certain cell so every sheet has the same structure.

    Another tip: try to use the macro recorder and take a look at the code. Easy to learn that way.

    Hope this gives you a start.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!