19 Everyday Use Excel VBA Codes

19 Everyday Use Excel VBA Codes

While working with excel, there are many excel tasks that you cannot perform using inbuilt Excel features and functions. Or accomplishing those tasks using Excel inbuilt features and functions may be a time-consuming and boring job. Surprisingly, Excel VBA Code can help you. In this blog, In this blog, I would like to share 19 of those most useful and everyday used excel VBA Codes that can make your excel task quick and easy.

Do you Need Excel VBA Codes Knowledge?

Answer is to this is – No 😎

This blog contains 19 different sections, each representing a particular task. In each of these sections, I have already written the VBA code lines to accomplish that task for you.

You need to simply copy those code lines, paste them into the Visual Basic Editor (VBE) window and finally run it (see the below section).

What is Excel Visual Basic Editor Window & How To Open?

The codes in the upcoming sections of this tutorial need to be pasted inside the VBE window before running them.

The questions that arise – what is VBE, how to access the VBE Editor window in Excel, and how to use the VBE Editor window to run the code.

To keep it simple, the VBE Editor window is a separate window in excel where you can write excel VBA codes and run them. The VBE Editor window contains all the required tools and options related to it.

To access the VBE Editor window, simply use the keyboard shortcut Alt + F11, and that’s it. A new window titled – ‘Microsoft Visual Basic for Applications’ would appear on your screen.

VBA Editor Window in Excel

In the VBE window, go to ‘Insert’ tab and choose the option ‘Module’, as shown below:

Insert Module in Excel VBA Editor Window

As a result, the silver area will become white, this is the code area where you actually write the VBA code.

Once you write or copy-paste the relevant from the upcoming section, simply run it using the green-colored ‘Run’ button.

VBA Code Area and Run button in Excel

Index – Everyday Use Excel VBA Codes

Now, when you have an understanding of what is VBA and how to run VBA codes, we are good, to begin with the ExcelUnlocked 19 Everyday Use Excel VBA Codes-

  • Hide all the worksheets except active worksheets in Excel
  • Unhide or show all the worksheets at one go
  • Unprotect all the worksheets in excel at one go
  • Protect all the worksheets in excel at one go
  • Show or display or turn on the gridlines for all the worksheets at one go
  • Hide or remove or turn off the gridlines for all the worksheets at one go
  • Reverse the order of the excel worksheets
  • Arrange or sort the worksheets in descending order (Z to A)
  • Arrange or sort the worksheets in ascending order (A to Z)
  • Create Index of worksheets (without a hyperlink)
  • Generate an index of worksheets (with a hyperlink to sheets)
  • Create worksheets based on an excel list (without a hyperlink)
  • Generate worksheets based on excel list (with a hyperlink to sheets)
  • Create worksheets based on an excel list (if the worksheet is not already created)
  • Delete worksheets that are mentioned in the excel list
  • Split each excel worksheet into an excel file or workbook
  • Separate text and number from the cell containing both text and numbers
  • Create folder based on excel list
  • Create a new folder for each worksheet in excel with the worksheet name

1# VBA Code to Hide all sheets except active worksheets

Open the Workbook in which you want to hide all the worksheets except for the active sheet in Excel.

Multiple Worksheets in Workbook Excel

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then, copy-paste the below code into the VBE Editor window.

Sub Hide_All_WS()
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> ActiveSheet.Name Then
            WS.Visible = xlSheetHidden
        End If
    Next WS

    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would instantly hide every worksheet in excel except the selected worksheet.

Hide All Worksheets except Active Worksheets Excel

2# VBA Code to Unhide or show all the worksheets at one go

Open the Workbook in which you want to display all the hidden worksheets at one go in Excel.

Hidden Sheets in Excel

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then, copy-paste the below code into the VBE Editor window.

Sub Unhide_All_WS()
    Application.ScreenUpdating = False
    Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets
        If WS.Visible = False Then
            WS.Visible = True
        End If
    Next WS

    Application.ScreenUpdating = True   
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would instantly unhide all the hidden worksheets at one click.

Multiple Worksheets in Workbook Excel

3# VBA Code to Unprotect all worksheets in excel at one go

Open the Workbook in which you want to unprotect all the worksheets.

Protected Worksheets in Excel

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then, copy-paste the below code into the VBE Editor window.

Sub Unprotect_All_Sh()
    Application.ScreenUpdating = False
    Dim Pwd As String
    Dim WS As Worksheet
    
    Pwd = VBA.InputBox("Enter Password to Unprotect Worksheet")
    
    For Each WS In ThisWorkbook.Worksheets
        If WS.ProtectContents = True Then
            WS.Unprotect Pwd
        End If
    Next WS
    
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. An input box would appear asking for a worksheet password. Enter the worksheet protection password and click OK.

Worksheet Unprotect Password Input box

As a result, excel would unprotect all the worksheets at one click.

4# VBA Code to Protect all the worksheets in Excel at one go

Open the Workbook in which you want to protect all the worksheets.

Then, use Alt + F11 to open the VBE Editor window and insert a new module in it. Copy-paste the below code into the VBE Editor window.

Sub Protect_All_Sh()
    Application.ScreenUpdating = False
    Dim Pwd As String
    Dim WS As Worksheet
    
    Pwd = VBA.InputBox("Enter Password to Protect Worksheet")
    
    For Each WS In ThisWorkbook.Worksheets
        If WS.ProtectContents = False Then
            WS.Protect Pwd
        End If
    Next WS
    
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. An input box would appear asking for a worksheet password. Enter the password of your choice and click OK.

As a result, excel would instantly protect all the worksheets at one click with the entered password.

5# VBA Code to Show Gridlines for all worksheets at one go

Open the workbook in which you want to show or display the excel gridlines.

Gridlines Not Visible in Excel Worksheet

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Show_Gridlines()
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        WS.Activate
        ActiveWindow.DisplayGridlines = True
    Next WS

    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would display and turn on all gridlines for all the worksheets in excel at one click.

Gridlines visible in all worksheets in excel

6# VBA Code to Hide Gridlines for all worksheets at one go

Open the workbook in which you want to hide the excel gridlines from all the worksheets.

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Hide_Gridlines()
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        WS.Activate
        ActiveWindow.DisplayGridlines = False
    Next WS

    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would instantly remove and turn off all gridlines for all the worksheets in excel at one click.

7# VBA Code to Reverse the order of the excel worksheets

Open the workbook in which you want to reverse the worksheet order.

Reverse the Order of Worksheets Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Revrs_WS_Order()
    Application.ScreenUpdating = False
    Dim WS_Cnt As Long
    Dim i As Long
    Dim r As Long
    
    WS_Cnt = Worksheets.Count
    r = 1
    
    For i = 1 To WS_Cnt - 1
        Worksheets(WS_Cnt).Move Before:=Worksheets(r)
        r = r + 1
    Next i

    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As soon as you run this code, excel would move the first sheet to last and likewise move the last sheet to the first place.

Reverse the Order of Worksheets Result

8# VBA Code to sort worksheets in Descending order (Z To A)

Open the workbook in which you want to arrange the worksheets in descending order.

Arrange Worksheets in Descending Order Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Sort_WS_Descending_ZA()
    Application.ScreenUpdating = False
    Dim Sh_Cnt As Long
    Dim i As Long
    Dim r As Long
    Dim z As Long
    
    Sh_Cnt = Worksheets.Count
    z = 1
    
    For i = 1 To Sh_Cnt - 1
        z = z + 1
        For r = z To Sh_Cnt
            If Worksheets(r).Name > Worksheets(i).Name Then
                Worksheets(r).Move Before:=Worksheets(i)
            End If
        Next r
    Next i

    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As soon as you run this code, excel would sort and arrange the worksheets in Z to A order.

Arrange Worksheets in Descending Order Result

9# VBA Code to sort worksheets in Ascending order (A To Z)

Open the workbook in which you want to arrange the worksheets in ascending order.

Arrange Worksheets in Ascending Order Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Sort_WS_Ascending_AZ()
    Application.ScreenUpdating = False
    Dim Sh_Cnt As Long
    Dim i As Long
    Dim r As Long
    Dim z As Long
    
    Sh_Cnt = Worksheets.Count
    z = 1
    
    For i = 1 To Sh_Cnt - 1
        z = z + 1
        For r = z To Sh_Cnt
            If Worksheets(r).Name < Worksheets(i).Name Then
                Worksheets(r).Move Before:=Worksheets(i)
            End If
        Next r
    Next i

	Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As soon as you run this code, excel would sort and arrange the worksheets in A to Z order.

Arrange Worksheets in Ascending Order Result

Open the workbook in which you want to generate an index of sheets name in a separate worksheet automatically (without hyperlink).

Create Index of Worksheet without hyperlink Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Index_Wthout_Link()
    Application.ScreenUpdating = False
    Dim WS_Cnt As Long
    Dim i As Long
    Dim Sh_Name As String

    Sh_Name = InputBox("Give a Name to Index Worksheet")
    WS_Cnt = Worksheets.Count
    Worksheets.Add Before:=Sheets(1)
    Worksheets(1).Name = Sh_Name

    For i = 1 To WS_Cnt
        Sheets(1).Range("A" & i).Value = Worksheets(i + 1).Name
    Next i

    VBA.MsgBox "Index Created with Sheet Name " & Sh_Name
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would pop out a window to provide the name of the index worksheet. Enter the name of your choice, like – My Index, and click OK.

Give Index Sheet Name Input Box

Finally, you would have the names of all the worksheets listed in a separate excel worksheets as shown below:

Create Index of Worksheet without hyperlink Result

Open the workbook in which you want to generate an index of sheets name in a separate worksheet automatically (with hyperlink).

Create Index of Worksheet

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Index_With_Link()
    Application.ScreenUpdating = False
    Dim WS_Cnt As Long
    Dim i As Long
    Dim Sh_Name As String

    Sh_Name = InputBox("Give a Name to Index Worksheet")
    WS_Cnt = Worksheets.Count
    Worksheets.Add Before:=Sheets(1)
    Worksheets(1).Name = Sh_Name

    For i = 1 To WS_Cnt
        Sheets(1).Range("A" & i).Value = Worksheets(i + 1).Name
        Sheets(1).Range("A" & i).Hyperlinks.Add _
            Anchor:=Sheets(1).Range("A" & i), Address:="", _
            SubAddress:="'" & Sheets(1).Range("A" & i).Value & "'!A1"
    Next i

    VBA.MsgBox "Index Created with Sheet Name - " & Sh_Name
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would pop out a window to provide the name of the index worksheet. Enter the name of your choice, like – My Index, and click OK.

Give Index Sheet Name Input Box

Finally, you would have the names of all the worksheets listed in a separate excel worksheets with hyperlink to individual sheets, as shown below:

Create Index of Worksheet with hyperlink Result

Click on any of the worksheet name and excel would take you directly to that worksheet tab. 🙂

Open the workbook in which you want to create individual worksheets based on the list in excel without hyperlink to sheets.

Note – The excel list of names must be placed in cell A1, as shown below:

Create Sheets from Excel list Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below code into the VBE Editor window.

Sub Create_WS_Wthot_Link()
    Application.ScreenUpdating = False
    Dim LstCell As Long
    Dim i As Long
    
    LstCell = ActiveSheet.Range("A1").End(xlDown).Row
    
    For i = 1 To LstCell
        Worksheets.Add After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Worksheets(1).Range("A" & i).Value
    Next i

    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would generate individual excel sheets for each of the names, like below:

Create Sheets from Excel list Result

Open the workbook in which you want to create individual worksheets based on the list in excel with a hyperlink to sheets.

Note – The excel list of names must be placed in cell A1, as shown below:

Create Sheets from Excel list Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Create_WS_With_Link()
    Application.ScreenUpdating = False
    Dim LstCell As Long
    Dim i As Long
    
    LstCell = Range("A1").End(xlDown).Row
    
    For i = 1 To LstCell
        Worksheets.Add After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Worksheets(1).Range("A" & i).Value
        Worksheets(1).Range("A" & i).Hyperlinks.Add _
           Anchor:=Worksheets(1).Range("A" & i), Address:="", _
           SubAddress:="'" & Worksheets(1).Range("A" & i) _
           .Value & "'!A1"
    Next i

    Worksheets(1).Activate
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would generate individual excel sheets for each of the names, like below:

Create Sheets from Excel list Result

It would also create hyperlink to the individual worksheets.

14# VBA Code to Create worksheets based on excel list if does not already exist

Open the workbook in which you want to create individual worksheets based on the list in excel if the worksheet does not already exist.

Note – The excel list of names must be placed in cell A1, as shown below. Also the worksheet containing list should be an active worksheet.

Create Sheets from Excel list Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Crt_Sh_If_DoentExst()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim WS As Worksheet
    Dim LstCell As Long
    
    LstCell = Worksheets(1).Range("A1").End(xlDown).Row
    
    For i = 1 To LstCell
        For Each WS In ThisWorkbook.Worksheets
            If Worksheets(1).Range("A" & i).Value = WS.Name _
                Then GoTo NextOne
        Next WS
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets(1).Range("A" & i).Value
NextOne:
    Next i
    
    Worksheets(1).Activate
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would generate individual excel sheets for each of the names, like below:

Create Sheets from Excel list Result

15# VBA Code to Delete worksheets that are mentioned in the excel list

Suppose, you have an excel list containing the names of the worksheets, and you want to delete those worksheets.

Delete Worksheets based on List Sample #2

Note – Make sure that the excel list is in cell A1, Also the worksheet containing the list should be an active worksheet, as shown below:

Delete Worksheets based on List Sample

The purpose here is to delete ‘Sheet B’ and ‘Sheet C’ and keep the others worksheets as it is.

Simply, use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Del_WS_From_List()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim WS As Worksheet
    Dim i As Long
    Dim LstCell As Long
    
    LstCell = Worksheets(1).Range("A1").End(xlDown).Row
    
    For i = 1 To LstCell
        For Each WS In ThisWorkbook.Worksheets
            If Worksheets(1).Range("A" & i).Value = WS.Name _
                Then WS.Delete
        Next WS
    Next i
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code. As a result, excel would delete the worksheets that are mentioned in the list and keep the rest as it is.

16# VBA Code to Split each excel worksheet into an excel file or workbook

Suppose you have different worksheets in excel workbook and you want to create a separate file or workbooks for each worksheets using VBA.

Split each excel worksheet into an excel file or workbook Sample

Use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Split_WS_Sep_File()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim WS As Worksheet
    Dim Folder As String
    
    Folder = ThisWorkbook.Path
    
    ThisWorkbook.Save
    
    For Each WS In ThisWorkbook.Worksheets
        WS.Copy
        ActiveWorkbook.SaveAs Folder & "\" & WS.Name & ".xlsx"
        ActiveWorkbook.Close
    Next WS
    
    VBA.MsgBox ("All the Files Saved in This Path - " & Folder)
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code.

As a result, excel would create new workbook for each worksheet in excel at the same location/folder.

Split each excel worksheet into an excel file or workbook Result

17# VBA Code to Separate Text and Number from Cells containing Text and Numbers

Suppose, you have some values containing characters and numeric values combination in column A, and you want to separate the characters and numbers into different cells.

See image below showing sample data with expected output/result.

Separate Character and Numeric Values in Different Cells Sample

Note – Make sure that the excel list is in cell A1, Also the worksheet containing list should be an active worksheet.

To do so, use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Extrct_Text_Num()
    Application.ScreenUpdating = False
    Dim Num As String, Text As String, Glb_Num As String, 	Glb_Text As String, Lst_Cell As Long, i As Long, r As 	Long, Char_Len As Long
    Lst_Cell = Range("A1").End(xlDown).Row
    For i = 1 To Lst_Cell
        Char_Len = VBA.Len(Range("A" & i).Value)
        For r = 1 To Char_Len
            If IsNumeric(Mid(Range("A" & i).Value, r, 1)) Then
                Num = Mid(Range("A" & i).Value, r, 1)
            Else
                Text = Mid(Range("A" & i).Value, r, 1)
            End If
            Glb_Num = Glb_Num & Num: Glb_Text = Glb_Text & Text
            Num = "": Text = ""
        Next r
        Range("B" & i).Value = Glb_Text
        Range("C" & i).Value = Glb_Num
        Glb_Num = "": Glb_Text = ""
    Next i
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code.

As a result, excel would separate the text and names into separate columns, as demonstrated in the above image.

18# VBA Code to Create Folder Based on Excel List in Computer

Suppose, you have an excel list in column A (list starting from A1) in Excel, and you want to create a separate folders based on this list.

Create Folders based on Excel List Sample

Note – Make sure that the excel list is in cell A1, Also the worksheet containing list should be an active worksheet.

To do so, use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Create_Folder()
    Application.ScreenUpdating = False
    Dim LstCell As Long
    Dim i As Long
    
    LstCell = ActiveSheet.Range("A1").End(xlDown).Row
    
    For i = 1 To LstCell
        On Error Resume Next
        VBA.MkDir ThisWorkbook.Path & "\" & Range("A" & i).Value
    Next i
    
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code.

As a result, excel would create separate folder in your computer based on the excel list, as shown below:

Create Folders based on Excel List Result

19# Create New Folder for Each Excel Worksheet with Folder Name as Sheet Name

The purpose here is to create folders for each of the worksheets in Excel (Folder name = Worksheet Name). See the sample image below-

Create Folder based on Excel Worksheet Name Sample

To do so, use Alt + F11 to open the VBE Editor window and insert a new module in it. Then copy-paste the below excel VBA codes into the VBE Editor window.

Sub Create_Folder()
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        VBA.MkDir ThisWorkbook.Path & "\" & WS.Name
    Next WS
    
    Application.ScreenUpdating = True
End Sub

Now, use the green-colored ‘Run’ button to execute the code.

As a result, excel would create separate folder in your computer for each individual worksheets.

Download PDF Version of this post on 19 Everyday Use VBA Codes.

Leave a Comment