VBA example - Excel 2007: VBA add hide delete sheets
VBA code to VBA add hide delete sheets In this VBA example code, you will learn to work with Excel sheet. The common operations of sheet object are adding sheets, hiding sheets, and deleting sheets.
-Add sheets: To add sheets to current Excel file (workbook) you can use the Add() method of the sheets collection object. The Add method method has the following prototype:
Worksheets.Add([Before], [After], [Count])
The Before argument represents the sheet name that will stay before the sheets to be added. The After argument is the name of the sheet that stays after the sheets to be added. The Count argument can be used to specify the number of sheets to be added.
-Hide sheets: A sheet or multiple sheets can be hidden by setting the Visible property of the Worksheets collection object to True.
-Delete sheets: You can delete a sheet or multiple sheets by using the Delete() method of the Worksheets collection object.
Note: to hide or delete multiple sheets you need to combine them by using Array object (see example code).
To run this example VBA code, you will need to copy and paste the example VBA code in the Activate procedure block of your worksheet. To open the Activate block, double-click the worksheet then in the objects dropdown list select Worksheet and in the Events dropdown list select Activate.
VBA code for this example:
Private Sub Worksheet_Activate()
'Add 10 sheets before sheet 3
Worksheets.Add Sheet3, , 10
'Hide sheet6 only
Worksheets("Sheet6").Visible = False
'Hide sheet7 and sheet8
Worksheets(Array("Sheet7", "Sheet8")).Visible = False
'Delete sheet9 only
'Delete Sheet8 And Sheet9