Excel Macros Help to Show and Hide Select Worksheets

[NeednHelp0]

I just started working on compiling all of the sub contractors and material vendors we have with pricing. What I would like to do is give each contractor their own work sheet page so I can keep up with pricing changes year to year. This will create a lot of worksheets in one work book. Would it be possible to have a main page with categories on it and when you click/select that category it will unhide the work sheets I have designated in that category? After which have a macro to hide all sheets again?

*I have not created the work book yet just trying to see if my idea is possible.

 

[Z51]

You definitely can. You can use a command button, or double-click event, or some other method of triggering the macro, and it can hide or un-hide specific sheets you want (or all sheets, or one sheet, etc.)

 

[NeednHelp0]

You definitely can. You can use a command button, or double-click event, or some other method of triggering the macro, and it can hide or un-hide specific sheets you want (or all sheets, or one sheet, etc.)

I am a rookie and just started trying to learn it. What would the formula be in order to make this work?

 

[Z51]

It wouldn't be a formula, you'd need to use a macro (VBA). For information about this, see here: VBA Hide (or Unhide) a Worksheet - VBA Code Examples

 

[NeednHelp0]

It wouldn't be a formula, you'd need to use a macro (VBA). For information about this, see here: VBA Hide (or Unhide) a Worksheet - VBA Code Examples

I apologize I meant the macro to target the specific sheets.

 

[Z51]

It all depends on your requirements as to which sheets to hide. You could have a macro look at a cell containing a category, and hide sheets based on that, or a drop-down list, or ... there's many ways. A basic hide sheet macro could be as simple as the following:

Public Sub hideSheets()
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
End Sub

To unhide them, change False to True

 

[NeednHelp0]

It all depends on your requirements as to which sheets to hide. You could have a macro look at a cell containing a category, and hide sheets based on that, or a drop-down list, or ... there's many ways. A basic hide sheet macro could be as simple as the following:

Public Sub hideSheets()
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
End Sub

To unhide them, change False to True

example:
I have multiple electrical contractors I have started naming each sheet by the company name
company1 company 2 company3

I also have multiple paint contractors
company4 company5 company6

How would I change the above formula to be initiated when a command button is pushed to show all the electrical contractors and hide the other sheets or vice versa?

 

[Z51]

There are many ways to do it, but it depends on your workbooks/worksheets layout.

For example, determine what denotes each sheet as being an electrical vs. paint vs. plumbing contractor. Perhaps it's a cell that simply says "Plumbing" or "Electrical", like cell A1.

You could create a user form, or inputbox, that would appear when running a macro to allow you to select a category, then based on that selection it would hide any sheet that has the matching value in A1.

You could have a clean sheet and type Electrical in cell A1, then run the macro and have it hide all sheets identified as that category.

I rarely visit here, just felt like spending some time helping out, but I'm usually not in the business of building workbooks. The original link I provided shows you some of the basics of hiding and un-hiding worksheets. I suggest you start there and then expand upon it. If you run into specific issues, that's where this forum is quite helpful.