Macro to show worksheets based on a a hidden sheet, referenced by code, that lists the sheets that should show when we hit a button.

preetypj

New Member
Joined
Aug 14, 2014
Messages
4
Hi, I have four buttons in my excel that show sheets using macro. The button works in the below way:
D1 - Shows Sheet1, Sheet 3, Sheet 7
D2 - Shows Sheet2, Sheet 4, Sheet 6
D3 - Shows Sheet5, Sheet 8,
D4 - Shows Sheet D9


For now we have hard-coded the values in the code about which sheets will open. Now we need to automate this so that there is an excel sheet A in the same file.
This sheet A will have list of button names and sheets to view. Our code should be taking the list of tabs from this sheet.
coding should be like which allow us to maintain this if we decide changes in grouping are needed.


D1 D2 D3 D4
Sheet 1 Sheet 2 Sheet 5 Sheet9
Sheet 3 Sheet 4 Sheet 8
Sheet 7 Sheet 6
Any help would be appreciated.


Thanks
Preeti Jha
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
For the first row (Row 2):

Rich (BB code):
Sub SheetsRow2()
    n = Sheets("Sheet A").Cells(2, Columns.Count).End(xlToLeft).Column
    For c = 1 To n
        Sheets(CStr(Sheets("Sheet A").Cells(2, c))).Visible = True
    Next
End Sub
Then you'd need to create new macros with the bold/italic parts altered for the other rows.

Hope this helps,

Chris.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top