Create a FREE Account

Excel Tutorials For Macros

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

Delete Hidden Worksheets

Edit

Add to Favorites

This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop up for each hidden sheet making sure that you want to delete it. This is a precaution which Microsoft Excel takes and not the actually macro itself. Though, this is good because it makes sure you think twice before deleting something that could have necessary or valuable information contained within it.

Where to install the macro: Module

Delete Hidden Worksheets


					
					Sub DeleteHiddenWorksheets()					  
					  'This Macro will remove all hidden worksheets from a workbook.

					    'There will be a warning window that pops up to make sure you want

					    'to do this just in case there is other information that is needed

					    'within those hidden sheets.
					  i = 1

					    While i <= Worksheets.Count

					    If Not Worksheets(i).Visible Then

					    Worksheets(i).Delete

					    Else

					    i = i + 1

					    End If

					    Wend
					  End Sub

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

Similar Content on TeachExcel

Delete Hidden Rows in a Workbook
Macro: This macro will delete hidden rows from every worksheet in an entire workbook. However...

Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
Macro: Delete all chart sheets and tabs in Excel using this macro. This will only delete the char...

Hide Data Within a Worksheet in Excel
Tutorial: In Excel you can actually hide data that is stored within a worksheet. This allows you to ...

Hide Formulas in a Worksheet and Prevent Deletion
Macro: This macro will hide all formulas within a workbook and not allow them to be deleted. The ...

Automatically Copy New Data to Another Worksheet
: This macro allows you to quickly and easily copy all new data from one worksheet to anoth...

Copy Data or Formatting to Multiple Worksheets in Excel
Tutorial: Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple...

How to Install the Macro

Select and copy the text from within the grey box above.

Open the Microsoft Excel file in which you would like the Macro to function.

Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions. Or For other ways to get there, Click Here.

On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
1. Go to the menu at the top of the window and click Insert > Module
2. Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
3. Go to Step 8.

If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
1. Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
2. Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
3. A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
4. Go to Step 8.

If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
1. Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
2. Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
3. A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
4. Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
5. Go to Step 8.

Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

You are now ready to run the macro.

Tutorial Details

Similar Content

Delete Hidden Rows in a Workbook
Macro: This macro will delete hidden rows from every worksheet in an entire workbook. However...

Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
Macro: Delete all chart sheets and tabs in Excel using this macro. This will only delete the char...

Hide Data Within a Worksheet in Excel
Tutorial: In Excel you can actually hide data that is stored within a worksheet. This allows you to ...

Excel Forum

Contact | Privacy Policy | Disclaimer | Terms
Copyright© 2020 TeachExcel.com