VBA-Lesson 3. Sheets collection

This collection is a set of sheets (Sheets) in a book (Workbooks). Let's see what actions we can do on sheets.

How to count the number of sheets in a book

First, let's try to find out how many sheets our book has:

Sub Test() 'moonexcel.com.ua
MsgBox (Str(Application.Workbooks.Item("Test.xls").Sheets.Count))
End Sub

With this code, we called a message to the screen (MsgBox), which displayed the number of sheets (Sheets.Count ) in the book (Workbooks) "Test.xls".

The sheet includes not only cells, but also diagrams. Also, like the calculation sheet, the chart will be included in the sheet count.

How to add a sheet to a book

There is also an opportunity to add your own sheets to the sheet collection, for this there is an Add method. This method has 4 parameters Add(Before, After, Count, Type). All these parameters are optional. The first two are responsible for the place of insertion of the sheet. Next, the number of sheets to be inserted Count and the sheet type Type. Types can be, for example, xlWorkSheet for a spreadsheet and xlChart for a chart. If the location is not specified, the sheet will be inserted relative to the current sheet.

Sub Test() 'moonexcel.com.ua
     Sheets.Add After:=Worksheets("Sheet3"), Count:=4
End Sub

In this way, we inserted 4 sheets (Count:=4) after the sheet "Sheet3". You can also insert a letter at the very end of the book:

Sub Test() 'moonexcel.com.ua
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
End Sub

How to hide a sheet

If you wish, some letters can be hidden. This is useful if you have constants or calculations that you do not want to see on the screen in the form of letters. You can use the Visible method for this. By setting this property to TRUE or FALSE you can hide or display the required sheet.

Sub Test() 'moonexcel.com.ua
ActiveWorkbook.Sheets("Sheet3").Visible = False
End Sub

Articles on the topic: