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
           
   
Worksheets("Sheet9").Delete
         
  
'Delete Sheet8 And Sheet9
         
   
Worksheets(Array("Sheet8", "Sheet9")).Delete

End Sub


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.