In the 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 you had requested them so I have clubbed all of them together and made one article 🙂 Enjoy reading this.

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