
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 😎
- Do you Need Excel VBA Codes Knowledge?
- What is Excel Visual Basic Editor Window & How To Open?
- Index – Everyday Use Excel VBA Codes
- 1# VBA Code to Hide all sheets except active worksheets
- 2# VBA Code to Unhide or show all the worksheets at one go
- 3# VBA Code to Unprotect all worksheets in excel at one go
- 4# VBA Code to Protect all the worksheets in Excel at one go
- 5# VBA Code to Show Gridlines for all worksheets at one go
- 6# VBA Code to Hide Gridlines for all worksheets at one go
- 7# VBA Code to Reverse the order of the excel worksheets
- 8# VBA Code to sort worksheets in Descending order (Z To A)
- 9# VBA Code to sort worksheets in Ascending order (A To Z)
- 10# VBA Code To Create index of worksheets without link
- 11# VBA Code to Create index of worksheets with link
- 12# VBA Code to Create Worksheets from Excel List without link
- 13# VBA Code to Create Worksheets from Excel List with link
- 14# VBA Code to Create worksheets based on excel list if does not already exist
- 15# VBA Code to Delete worksheets that are mentioned in the excel list
- 16# VBA Code to Split each excel worksheet into an excel file or workbook
- 17# VBA Code to Separate Text and Number from Cells containing Text and Numbers
- 18# VBA Code to Create Folder Based on Excel List in Computer
- 19# Create New Folder for Each Excel Worksheet with Folder Name as Sheet Name
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.
Also Read: Start Automation – Record A Macro in Excel
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.

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

As a result, the silver area will become white, this is the code area where you actually write the VBA code.
Also Read: Four Ways to Run A Macro in Excel
Once you write or copy-paste the relevant from the upcoming section, simply run it using the green-colored ‘Run’ button.

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.

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.

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.

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.

3# VBA Code to Unprotect all worksheets in excel at one go
Open the Workbook in which you want to unprotect 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 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.

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.

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.

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.

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.

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.

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.

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.

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.

10# VBA Code To Create index of worksheets without link
Open the workbook in which you want to generate an index of sheets name in a separate worksheet automatically (without hyperlink).

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.

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

11# VBA Code to Create index of worksheets with link
Open the workbook in which you want to generate an index of sheets name in a separate worksheet automatically (with hyperlink).

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.

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

Click on any of the worksheet name and excel would take you directly to that worksheet tab. 🙂
12# VBA Code to Create Worksheets from Excel List without link
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:

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:

13# VBA Code to Create Worksheets from Excel List with link
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:

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:

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.

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:

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.

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:

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.

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.

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.

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.

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:

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-

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.