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

Excel 2010 VBA objects: Handling workbooks

In a previous post I gave an introduction about VBA objects. In this post I will zoom a little bit into objects related to the workbooks. Remember that these posts are not intended to teach programming, but will rather give some highlights on the subject.

Let us start with an example:

Suppose we want to open a workbook called “Test.xlsx”, Get the workbook name with full path, find out how many worksheets are in the workbook and then close the workbook. We will use the following VBA code:

SUB ProcessWorkBook
Workbooks.Open Filename:=”Test.xlsx”   ‘open the  workbook
Once the workbook is open, we will use the ActiveWorkbook property of the Application object
strPath = ActiveWorkbook.FullName  ‘Get workbook name with full path
intSheets= ActiveWorkbook.Worksheets.Count  ‘get the count of worksheets in this workbook
Workbooks(“Test.xlsx”).Close  ‘close the  workbook
Msgbox strPath     ‘ Display name and path
Msgbox intSheets  ‘Display No. of worksheets
END SUB

Other methods that you can use:

‘To open a workbook as read only:
Workbooks.Open Filename:=”Test.xlsx”, ReadOnly:=True
‘To create a new workbook
Workbooks.Add

Once the workbook is open, then you can access the ActiveWorkbook property. There are a number of variables that you can retrieve e.g.

ActiveWorkbook.Name ‘Get workbook name
ActiveWorkbook.Path ‘Get workbook full path

You can also perform the following actions:

ActiveWorkbook.PrintPreview ‘print preview the workbook
ActiveWorkbook.SaveAs ‘save workbook with different name
ActiveWorkbook.Close ‘close the workbook

 

How to rename a worksheet

To rename the active worksheet use one of these two methods:

Method 1

1. On the format menu point to Sheet then click Rename. The sheet tab name will be highlighted (edit mode).
2. Type the new name, then click anywhere outside the tab name.

rename1

Method 2

Simply double-click the sheet tab name, to get into edit mode, and then type the new name.

rename2

How to view hidden sheets in a workbook

To view hidden sheets in a workbook, you need to unhide them first.

To unhide a worksheet:

1. In the Format menu point to Sheet then click Unhide.

unhidesheet1

2. The Unhide window will open, with the hidden sheets listed.

3. Select the sheet you want to unhide then click OK.

unhidesheet2

Applies to: Excel 2003


How to print all sheets in a workbook

To print all worksheets in a workbook then :

1. In the File menu click Print.

2. The Print window will open.

3. At the bottom of the Print window find a section labeled: Print what .

4. Click on the radio button labeled: Entire workbook.

5. Click OK.

Applies to Excel 2003

How to copy or move worksheets within the same workbook

To copy or move worksheets within the same workbook follow these steps:

1. Switch to the workbook which has the sheet to be copied or moved.

2. in the Edit menu click Move or copy sheet.

3. The Move or copy dialogue will open.

4. The To book drop down will default to the current workbook. Leave this as it is.

5. in the Before sheet list click where you want the sheet to be moved (position within the workbook).

6. Select the Create a copy check box if you want to retain the original worksheet , or deselect it if you just want to move the worksheet to a new location withinthe workbook.

Applies to Excel 2003


How to print more than one sheet in a workbook

To print more than one worksheet in a workbook then :

1. Select the worksheets you want to print.

  1. If the sheets to be printed are adjacent then click on the first one, press SHIFT then click the last one.
  2. If they are not then click on the first one, press CTRL then click the others one at a time.

2. In the File menu click Print.

3. The Print window will open.

4. At the bottom of the Print window find a section labeled: Print what .

5. Click on the radio button labeled: Active sheet(s) .

6. Click OK.

Applies to Excel 2003

How to copy or move worksheets between workbooks

To copy or move worksheets between workbooks follow these steps:

1. Switch to the workbook which has the sheet to be copied or moved.

2. in the Edit menu click Move or copy sheet.

3. The Move or copy dialogue will open.

4. In the To book drop down select the destination workbook. You can also choose to create a new workbook.

5. in the Before sheet list click where you want the sheet to be moved (position within the workbook).

6. Select the Create a copy check box if you want the worksheet to remain in the original workbook, or deselect it  to move the worksheet completely and delete it from the original workbook.

Applies to Excel 2003