How to Unhide All Sheets in Excel Using VBA Script
Suppose you have an Excel Workbook with multiple sheets. If you want to hide all sheets except one active sheet then you can use this code.
It is very easy to code. Just follow the below steps and you are done.
Step 1: You have to open Visual Basic Editor in Excel. Press the key Alt+F11.
Step 2: Now insert the new Module in Editor Window. Click on Insert and select Module.
Step 3: After the new module is added, just copy the below code, and you are done:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Sub UnhideAllSheets() 'Variable Declaration Dim ws As Worksheet 'For Loop will find every hidden sheet in the workbook and unhide one by one all sheets. 'It will run untill all sheets are visible. For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws 'Type the sheet name on which you want cursor active or active sheet. Sheets("Sheet1").Select End Sub |
Step 4: After adding the code, save the file. Go to the File menu and click on Save or press Ctrl+S.
Step 5: After saving the file, return to excel and you can run your macro from the macro options. So Go to the File menu and Click on the last option Close and Return to Microsoft Excel or press Ctrl+Q.
Note:- If you want to edit VBA code, then follow Step 1 and after editing, follow Steps 4 and 5.
Assign a shortcut key to Macro
Go to the Developer menu and click on Macros. A new small window will open then click on Option and another smaller window will open.
Now press any key you want to create as a shortcut key. For example, if you want that macro should be run by pressing Ctrl+G, then press G and click OK.
Assign a customized click button to Macro
Go to the Insert menu and then click on Shapes and select any image you want to make as Button and place it anywhere in the excel sheet.
Right-click on the image button and click on Assign Macro.
A small window will open. Just select the macro name and click OK.
Now if you will press Ctrl+G or click on the button, the macro will run.
0 Comments