Dear LEM Readers,
In this first part of this article I am going to share very small but useful vba codes which are most frequently used in day-to-day vba programming. Many of my readers had requested for them so I have clubbed all of them together and made one article 🙂 Enjoy reading them…
Closing All Open Workbooks
You can call the below function to close all open workbooks:
Public Sub CloseAllWorkbooks() Dim Wb As Workbook ' Workbooks is the collection of all open workbooks For Each Wb In Workbooks If Wb.Name <> ThisWorkbook.Name Then Wb.Close savechanges:=True 'Set False if you want them to close without saving it End If Next Wb ' This statement will close the current workbook ' where vba code is running. Below stattement can ' not be written before ThisWorkbook.Close savechanges:=True End Sub
Closing All Inactive Workbooks
You can call the below function to close all inactive workbooks:
Public Sub CloseAllInactiveWorkbooks() Dim Wb As Workbook Dim WbName As String WbName = ActiveWorkbook.Name ' Workbooks is the collection of all open Workbooks For Each Wb In Workbooks If Wb.Name <> WbName Then Wb.Close savechanges:=True End If Next Wb End Sub
Saving All Open Workbooks
Below code will save all the open workbooks:
Public Sub SaveAllWorkbook() Dim WB As Workbook For Each WB In Workbooks WB.Save Next WB End Sub
Get the name of First Sheet in a Workbook
Keyword Sheets is a collection of all Sheets and WorkSheets of a workbook. Each sheet can be referred by passing the index number.
For example: First sheet can be referred as Sheets(1), Second as Sheets(2) and so on…
Below code will give you name of the first sheet in your workbook.
Public Sub FirstSheetName() Dim firstSheet As String firstSheetName = Sheets(1).Name End Sub
Get the name of Last Sheet in a Workbook
Below code will give you name of the last sheet in your workbook.
Note: Count is the property of Object Sheets which returns the total number of sheets available in the workbook
Public Sub LastSheetName() Dim firstSheet As String firstSheetName = Sheets(Sheets.Count).Name End Sub