VBA Codes for Inserting, Renaming, Moving, and Deleting Sheets – Streamline Excel Tasks with 4 Effective VBA Functions

VBA Codes for Inserting, Renaming, Moving, and Deleting Sheets

VBA, or Visual Basic for Applications, is a programming language used to automate tasks in Microsoft Office applications, including Excel. In this blog, we will show examples of VBA codes to insert, rename, move, and delete sheets in an Excel workbook. We can use these examples to simplify routine tasks and save time in your work.

VBA Codes for Inserting Sheets:

Inserting a new sheet is useful when you want to add more data or create a new section in your workbook. For example, you might want to add a new sheet to track expenses for a specific project or create a separate sheet to summarize data from multiple sheets.

To insert a new sheet, use the following VBA code:

Sub InsertSheet()
   Sheets.Add After:=Sheets(Sheets.Count)
End Sub

This code will add a new sheet after the last sheet in the workbook. You can change the position of the new sheet by modifying the “After” parameter.

RVBA Codes for renaming Sheets:

Renaming a sheet is useful when you want to give a more descriptive name to a sheet or change the name to better reflect the contents of the sheet. For example, you might want to rename a sheet to “Sales Report 2022” to make it clear what the sheet contains.

To rename a sheet, use the following VBA code:

Sub RenameSheet()
 Dim sName As String
 sName = InputBox("Enter new sheet name:")
 If sName="" Then
  ActiveSheet.Name = sName
 End If
End Sub

This code will prompt the user to enter a new name for the current active sheet. If the user enters a valid name, the sheet will be renamed accordingly.

VBA Codes for Moving Sheets:

Moving a sheet is useful when you want to reorganize your workbook or group related sheets together. For example, you might want to move all the sheets related to a specific project to the beginning of the workbook.

To move a sheet to a new position, use the following VBA code:

Sub MoveSheet()
Dim sName As String
Dim sPos As Integer
sName = InputBox("Enter sheet name to move:")
sPos = InputBox("Enter position to move to:")
If sName ="" And IsNumeric(sPos) Then
Sheets(sName).Move Before:=Sheets(sPos)
End If
End Sub

This code will prompt the user to enter a new name for the current active sheet. If the user enters a valid name, the sheet will be renamed accordingly.

VBA Codes for Deleting Sheets:

Deleting a sheet is useful when you no longer need a sheet or want to remove outdated information from your workbook. For example, you might want to delete a sheet that contains data from a previous year or a sheet that is no longer relevant to your work.

To delete a sheet, use the following VBA code:

Sub DeleteSheet()
 Dim sName As String
 sName = InputBox("Enter sheet name to delete:")
 If sName = "" Then
  Application.DisplayAlerts = False
  Sheets(sName).Delete
 Application.DisplayAlerts = True
 End If
End Sub

This code will prompt the user to enter the name of the sheet to delete. If the user enters a valid name, the sheet will be deleted. The DisplayAlerts property is used to prevent Excel from displaying a warning message before deleting the sheet.

In conclusion, these examples of VBA codes to insert, rename, move, and delete sheets in an Excel workbook can be useful for automating routine tasks and saving time. Users can easily modify these codes to suit their specific needs and start using them in their work.

To learn more about VBA and Excel, you can check the below websites:

1. www.thedatalabs.org
2. www.pk-anexcelexpert.com

Let us know if you are looking for some other code, we will post in comment section.