Managing Worksheets in Excel VBA

The Worksheets object is a collection that contains all the worksheets in the workbook.
There are several options to modify a single worksheet:

• ActiveSheet: the currently active worksheet
• Worksheets(Index): Index is the sequence number of the worksheet in the worksheets collection
• Worksheets("Name"): the worksheet’s name as a string (in quotation marks)

Adding a New Worksheet

The following procedure inserts a new worksheet:

Sub insertSheet()
    Worksheets.Add
End Sub

Comments

• By default, Excel offers three worksheets when creating a new workbook. If you want to add more, use the Add method.
• The Add method creates a new worksheet. The new worksheet becomes the active sheet. Its syntax is:
  expression.Add (Before, After, Count, Type):
  • Before: specifies the sheet before which the new sheet is added.
  • After: specifies the sheet after which the new sheet is added.
  • Count: number of sheets to add. Default is the number of selected sheets.
  • Type: specifies the sheet type. It can be one of the XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. To insert a sheet based on an existing template, specify the template’s path. Default is xlWorksheet.

If you want to insert a sheet at a specific position:

Sub insertSheet2()
    Worksheets.Add Before:=ActiveWorkbook.Worksheets(1)
End Sub

Comments

• The new worksheet is inserted at the beginning of the workbook, i.e., as the first worksheet.
• The previous worksheet at index 1 is moved one position to the right.

To insert a sheet at the end:

Sub insertSheet3()
    Worksheets.Add After:=Worksheets(Worksheets.Count)
End Sub

Comments

• To determine the position where the new sheet should be inserted, you must first know how many worksheets are already in the workbook. The Count property helps you do this.
• Then, just provide the After argument and the new sheet will be added as the last worksheet.

Renaming a Worksheet

This procedure renames a new worksheet in the workbook:

Sub CreateRenameSheet()
    ThisWorkbook.Activate
    MsgBox Worksheets.Count
    Worksheets.Add
    ActiveSheet.Name = "Elie"
    MsgBox Worksheets.Count
End Sub

Comments

• First, the number of worksheets is retrieved using the Count property.
• The Add() method is called to insert a new worksheet before the active one. The new sheet becomes the active one.
• ActiveSheet refers to the currently active worksheet. The sheet name can be retrieved or modified.
• The sheet count is displayed again for verification; it has increased by 1.

Another macro:

Sub RenameSheet()
'Step 1: Specify what Excel should do in case of an error
    On Error GoTo MyError
'Step 2: Add a new sheet and rename it
    Sheets.Add
    ActiveSheet.Name = WorksheetFunction.Text(Now(), "d-m-yyyy     hh_mm_ss ")
    Exit Sub
'Step 3: If error occurs, inform the user
MyError:
    MsgBox "A sheet with this name already exists."
End Sub

Comments

• Here, we anticipate a possible error if the new sheet gets an already existing name. The On Error statement handles this.
• The default name of the new sheet is SheetN. The code changes this using the Name property, based on the current date and time.
• On Error prevents step 3 from executing unless an error actually occurs.

To rename Sheet3 to the current date:

Sub sheetNameDate()
    On Error Resume Next
    Worksheets("Sheet3").Name = Date
End Sub

Comments

• The current date is assigned as the new sheet name using the Name property.
• Excel gets the date from the Windows system clock. On Error handles the case where Sheet3 doesn’t exist.

To rename a sheet based on cell B1 content:

Sub sheetNameFromCell()
    Worksheets(1).Name = Range("B1").Value
End Sub

Comments

• Worksheets(1).Name refers to the leftmost sheet. Similar to Worksheets("Sheet1").Name.

To name the first sheet based on the user and current date:

Sub sheetNameUser()
    Worksheets(1).Name = Application.UserName & "," & Date
End Sub

Comments

• The new name combines the username and the current date using the UserName property and Date.
• You can check the username via File > Options > General tab.

Deleting Worksheets

To delete a worksheet:

Sub deleteSheet()
    On Error GoTo errorHandler
    Sheets("Sheet1").Delete
    Exit Sub
errorHandler:
    MsgBox "There is no sheet to delete."
End Sub

Comments

• On Error redirects the flow if the specified sheet doesn’t exist.
• Exit Sub stops the macro after a successful deletion.
• An error message is shown otherwise.

Delete Without Confirmation

Sub deleteSheetSilently()
    Application.DisplayAlerts = False
    Sheets(1).Delete
End Sub

Comments

• DisplayAlerts = False suppresses confirmation prompts.
• It is True by default, which normally shows the « Are you sure? » message.

Delete All Sheets Except the Active One

Sub deleteAllSheetsExceptActive()
    Dim mysheet As Worksheet
    For Each mysheet In ThisWorkbook.Worksheets
        If mysheet.Name <> ThisWorkbook.ActiveSheet.Name Then
            Application.DisplayAlerts = False
            mysheet.Delete
            Application.DisplayAlerts = True
        End If
    Next mysheet
End Sub

Comments

• Declares a variable mysheet.
• Loops through all sheets in ThisWorkbook (the workbook containing the code).
• Compares each name to the active sheet. If different, it is deleted.

Delete All Empty Worksheets

Sub deleteEmptySheets()
    Dim i As Integer
    Application.DisplayAlerts = False
    On Error Resume Next
    For i = ActiveWorkbook.Sheets.Count To 1 Step -1
        Sheets(i).Activate
        If ActiveCell.SpecialCells(xlLastCell).Address = "$A$1" Then Sheets(i).Delete
    Next i
    Application.DisplayAlerts = True
End Sub

Comments

• Uses Count to determine how many sheets exist.
• Loops backward and checks if the last used cell is A1.
• If so, the sheet is considered empty and is deleted.

Activate a Worksheet

Sub ActivateSheet()
    ThisWorkbook.Activate
    Worksheets("Sheet3").Activate
    MsgBox ActiveSheet.Name
    Worksheets("Sheet1").Activate
    MsgBox ActiveSheet.Name
End Sub

Comments

• Activates specific sheets and displays their names.

To activate the previous sheet:

Sub activatePreviousSheet()
    On Error Resume Next
    ActiveSheet.Previous.Activate
End Sub

To activate the next sheet:

Sub activateNextSheet()
    On Error Resume Next
    ActiveSheet.Next.Activate
End Sub

Copying and Moving Worksheets

Copy a Worksheet

Sub CopySheet()
    ThisWorkbook.Activate
    Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
    ActiveSheet.Name = "Inventory"
End Sub

Comments

• Copy duplicates Sheet1 and places it after Sheet3.
• If no destination is given, a new workbook is created.

To copy used range from Sheet1 to Sheet2:

Sub CopyRangeSheet()
    Worksheets("Sheet1").UsedRange.Copy
    Worksheets("Sheet2").Paste Worksheets("Sheet2").Range("A1")
    Application.CutCopyMode = False
End Sub

To transfer data without using Copy:

Sub transferSheet()
    Dim sheet1 As Worksheet
    Dim sheet2 As Worksheet
    Dim i As Integer
    Dim y As Integer
    Set sheet1 = ThisWorkbook.Worksheets("Sheet1")
    Set sheet2 = ThisWorkbook.Worksheets("Sheet2")
    For i = 1 To sheet1.UsedRange.Rows.Count
        y = y + 1
        sheet2.Cells(i, 1) = sheet1.Cells(y, 1)
    Next i
End Sub

Move a Worksheet

Sub MoveSheet()
    ThisWorkbook.Activate
    Worksheets("Inventory").Move Before:=Worksheets("Sheet1")
End Sub

Move the Active Worksheet

Sub MoveActiveSheet()
    'Move active sheet to the end
    ActiveSheet.Move After:=Worksheets(Worksheets.Count)
    'Move active sheet to the beginning
    ActiveSheet.Move Before:=Worksheets(1)
End Sub

Transfer Sheet Without Formulas or Links

Sub transferSheetValuesOnly()
    Cells.Copy
    Application.Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Comments

• Copies all cells in the current sheet.
• Pastes only values (no formulas or links) into a new workbook.