Working with Workbooks and Sheets

The examples in this section demonstrate various ways to use VBA to work with workbooks and worksheets.

Saving all workbooks

The following procedure loops through all workbooks in the Workbooks collection and saves each file that has been saved previously:

 Public Sub SaveAllWorkbooks()     Dim Book As Workbook     For Each Book In Workbooks         If Book.Path <> "" Then Book.Save     Next Book End Sub 

Notice the use of the Path property. If a workbook's Path property is empty, the file has never been saved (it's a new workbook). This procedure ignores such workbooks and saves only the workbooks that have a non-empty Path property.

Saving and closing all workbooks

The following procedure loops through the Workbooks collection. The code saves and closes all workbooks.

 Sub CloseAllWorkbooks()     Dim Book As Workbook     For Each Book In Workbooks         If Book.Name <> ThisWorkbook.Name Then             Book.Close savechanges:=True         End If     Next Book ThisWorkbook.Close savechanges:=True End Sub 

The procedure uses an If statement within the For-Next loop to determine whether the workbook is the workbook that contains the code. This is necessary because closing the workbook that contains the procedure would end the code, and subsequent workbooks would not be affected.

Hiding all but the selection

The example in this section hides all rows and columns except those in the current range selection. Figure 11-12 shows an example.

Figure 11-12: All rows and columns are hidden, except for a range (G8:K17).

 Sub HideRowsAndColumns()     Dim row1 As Long, row2 As Long     Dim col1 As Long, col2 As Long     If TypeName(Selection) <> "Range" Then Exit Sub '   If last row or last column is hidden, unhide all and quit     If Rows(Rows.Count).EntireRow.Hidden Or _       Columns(Columns.Count).EntireColumn.Hidden Then         Cells.EntireColumn.Hidden = False         Cells.EntireRow.Hidden = False         Exit Sub     End If     row1 = Selection.Rows(1).Row     row2 = row1 + Selection.Rows.Count - 1     col1 = Selection.Columns(1).Column     col2 = col1 + Selection.Columns.Count - 1     Application.ScreenUpdating = False     On Error Resume Next '   Hide rows     Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True     Range(Cells(row2 + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True '   Hide columns     Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True     Range(Cells(1, col2 + 1), Cells(1, Columns.Count)).EntireColumn.Hidden = True End Sub 

If the range selection consists of a noncontiguous range, the first Area is used as the basis for hiding rows and columns.

CD-ROM  

A workbook with this example is available on the companion CD-ROM. The file is named  hide rows and columns.xlsm .

Synchronizing worksheets

If you use multisheet workbooks, you probably know that Excel cannot synchronize the sheets in a workbook. In other words, there is no automatic way to force all sheets to have the same selected range and upper-left cell . The VBA macro that follows uses the active worksheet as a base and then performs the following on all other worksheets in the workbook:

• Selects the same range as the active sheet.

• Makes the upper-left cell the same as the active sheet.

Following is the listing for the subroutine:

 Sub SynchSheets() '   Duplicates the active sheet's active cell and upper left cell '   Across all worksheets     If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub     Dim UserSheet As Worksheet, sht As Worksheet     Dim TopRow As Long, LeftCol As Integer     Dim UserSel As String     Application.ScreenUpdating = False '   Remember the current sheet     Set UserSheet = ActiveSheet '   Store info from the active sheet     TopRow = ActiveWindow.ScrollRow     LeftCol = ActiveWindow.ScrollColumn     UserSel = ActiveWindow.RangeSelection.Address '   Loop through the worksheets     For Each sht In ActiveWorkbook.Worksheets         If sht.Visible Then 'skip hidden sheets             sht.Activate             Range(UserSel).Select             ActiveWindow.ScrollRow = TopRow             ActiveWindow.ScrollColumn = LeftCol         End If     Next sht '   Restore the original position     UserSheet.Activate     Application.ScreenUpdating = True End Sub 

CD-ROM  

A workbook with this example is available on the companion CD-ROM in a file named  synchronize sheets.xlsm .