Microsoft Office Tutorials and References
In Depth Information
Working with Workbooks and Sheets
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 newly created 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 statement is necessary because closing the
workbook that contains the procedure would end the code, and subsequent workbooks wouldn’t
be affected. After all the other workbooks are closed, the workbook that contains the code closes
itself.
Hiding all but the selection
The example in this section hides all rows and columns in a worksheet except those in the current
range selection. Figure 11-12 shows an example.
 
Search JabSto ::




Custom Search