The examples in this section demonstrate various ways to use VBA to work with workbooks and worksheets.
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.
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.
The example in this section hides all rows and columns except those in the current range selection. Figure 11-12 shows an example.
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 . |
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 . |