Excel 2010 VBA objects: Handling worksheets

In a previous post we discussed how to handle workbooks through VBA code. In this post we will go one step furher to deal with worksheets.

To add a new worksheet:

ActiveWorkbook.Worksheets.Add

To activate a worksheet:

ActiveWorkbook.Worksheets(1).Activate   ‘specify worksheet by index number
ActiveWorkbook.Worksheets(“sheet12″).Activate   ‘specify worksheet by name

To delete a worksheet:

ActiveWorkbook.Worksheets(“sheet8″).Delete    ‘delete worksheet by name
ActiveWorkbook.ActiveSheet.Delete    ‘delete the active worksheet

To get the name of the active worksheet

ActiveWorkbook.ActiveSheet.Name

To print preview a worksheet

ActiveWorkbook.Worksheets(“sheet5″).PrintPreview

In the following example the ListWorksheets macro will loop through all the worksheets in the active workbook to retrieve their names and then, at the end of the loop, list all the names in a message box.

Sub ListWorksheets()
For i = 1 To ActiveWorkbook.Worksheets.Count
strNames = strNames & Chr(13) & ActiveWorkbook.Worksheets(i).Name
Next i
MsgBox strNames
End Sub

The next example, which you can find in the download page of this site, prints the hidden sheets in a workbook:

Sub PrintHiddenSheets()
‘*********************************************************
‘ Print only hidden sheets in the active workbook *
‘*********************************************************
Dim wSheet As Worksheet
Dim CurStat As VariantFor Each wSheet In ActiveWorkbook.Worksheets
If Not wSheet.Visible Then
CurStat = wSheet.Visible
wSheet.Visible = xlSheetVisible
wSheet.PrintOut
wSheet.Visible = CurStat
End If
Next
End Sub