How to add sheet to workbook: VBA, Excel

How to add sheet to workbook: VBA, Excel

In this article we will show you how to add a sheet to an Excel workbook using VBA. There are 2 different methods detailed below in order to do this.

To add a sheet to a workbook using VBA:

Depending on the result you want to achieve, you can either use the:

  • Copy method => For a copy of an existing sheet
  • Add method => To add a new blank sheet to your workbook.

You must also make sure that the name of the new sheet is compliant. That is to say, the name of the sheet should not be identical to an existing one or contain any forbidden characters. In both cases, the verification methods should be the same.

Copy method

Copy a sheet. The data and format of the new sheet will be identical to the source sheet.

Syntax

expression .Copy(Before, After) Expression can either be a Sheet or Worksheet object. The Before and After parameters are optional. You can only specify one of these two parameters. They are of the Variant type and can either represent the sheet locate before or after the one you want to copy. If the parameter is omitted, the sheet is automatically copied into a new workbook.

What happens then?

When creating a new sheet with the Copy method:

  • The sheet is created at the desired location (before or after another sheet in the workbook)
  • It is renamed based on the name of the source sheet. Example: Sheet1 => Sheet1(2)
  • It is identical to the copied sheet.
  • It becomes the active sheet. If the sheet is copied to a new workbook, it becomes the active workbook.

Examples of use

Copy the "Sheet1" after "Sheet3"

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

Copy the "Sheet4" before the "Sheet2":

Sheets("Sheet4").Copy Before:=Sheets("Sheet2")

Copy the "recap" sheet in a new workbook

Worksheets("recap").Copy

Copy the sheet indexed as 1 at the last position of the workbook, regardless of the name of the last sheet:

Sheets(1).Copy After:=Sheets(Sheets.Count)

Copy the sheet indexed as 10 at the beginning of the workbook, regardless of the name of the last sheet:

Sheets(10).Copy Before:=Sheets(1)

You want to copy multiple sheets of your active workbook into a new workbook:

Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Copy

The Add Method

Creates a new spreadsheet (graphics or macro). The new worksheet becomes the active sheet. This new sheet doesn't contain any data or formatting.

Syntax

expression .Add(Before, After,Count,Type) Expression can either be a Sheet or Worksheet object. The Before, After, Count and type parameters are optional. Either and Before - You can only specify one of these parameters. They are of the Variant type and can either represent the sheet locate before or after the one you want to copy. The Count parameter is also a Variant. It represents the number of sheets you want to add. The Type parameter represent the type of sheet you want to add.

  • xlWorkSheet - adds a worksheet
  • xlChart - adds a chart sheet
  • xlExcel4MacroSheet - adds a macro sheet (Excel4)
  • xlExcel4IntlMacroSheet - adss a macro sheet
  • xlDialogSheet - adds a dialog sheet.

NB: Don't forget to rename your parameters or the syntax will return the below error:

ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count), , 1, xlChart

Examples of use

Put a sheet after the last sheet in the workbook:

Sheets.Add After:=Worksheets(Worksheets.Count)

Adds three sheets at the "first position" of the active workbook:

 ActiveWorkbook.Sheets.Add Before:=Worksheets(1), Count:=3

Adds a chart sheet in a workbook named "Wbk18" (Open!) after the sheet named "Sheet4"

WorkBooks("Wbk18").Sheets.Add After:=Worksheets("Sheet4"), Type:=xlChart

Testing the sheet

Check if sheet already exists in the workbook

'Test si la feuille existe déjà
Function Feuil_Exist(strWbk As String, strWsh As String) As Boolean

'Gestionnaire d'erreur
On Error Resume Next
    '"Test"
    Feuil_Exist = (Workbooks(strWbk).Sheets(strWsh).Name = strWsh)
End Function

Check if the filename contains forbidden characters

'Test si la chaine contient un caractère à éviter
Function Valid_Name(strName As String, strChr As String) As Boolean
Dim i As Byte, Tb_Car() As String, strProhib As String

strProhib = "/\:*?""<>|" ' Liste des caractères à éviter
Tb_Car = Split(StrConv(strProhib, vbUnicode), Chr$(0))
'Boucle sur tous les caractères à éviter
 'Nota : le -1 est dû au Split de la chaine par le séparateur Chr(0)
 'En effet, la chaine se terminant par un Chr(0) il convient d'exclure ce dernier caractère
For i = LBound(Tb_Car) To UBound(Tb_Car) - 1
    'Test si la chaîne contient un caractère prohibé
    If InStr(strName, Tb_Car (i)) > 0 Then
 'Si oui : Return False
        Valid_Name = False
 'ET Retourne le caractère prohibé
        strChr = Tb_Car(i)
        Exit Function
    End If
Next i
'Si OK : Return True
Valid_Name = True
End Function

Calling the verification function

The code is the same for both the Copy or Add method.

Sub Principale()
Dim strNewName As String, strCara As String

strNewName = "NewSheet"
If Valid_Name(strNewName, strCara) = False Then
    MsgBox "Le nom : " & strNewName & " est invalide." & vbCrLf & _
            "Un nom de feuille ne peut pas contenir le caractère : " & strCara, vbCritical
    Exit Sub
End If
If Feuil_Exist(ThisWorkbook.Name, strNewName) = True Then
    MsgBox "Le nom : " & strNewName & " est invalide." & vbCrLf & _
            "Ce nom de feuille est déjà utilisé dans ce classeur.", vbCritical
    Exit Sub
End If
ThisWorkbook.Sheets.Add 'Ou : ThisWorkbook.Sheets("Feuil1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = strNewName
End Sub

Some useful tips for Office 2013

Office 2013 brings two complementary methods to those seen previously:

  • Copy a range of cells to a sheet and multiple sheets
  • The Add2 method for collections of Charts objects.

Copy a range of cell using the FillAcrossSheets method

This method is only available for Office 2013. You want to copy range of cells from "Sheet1" to "Sheet3", "Sheet5" and "Sheet7".

Sheets = Array("Sheet3", "Sheet5", "Sheet7")
Sheets(Sheets).FillAcrossSheets Worksheets("Sheet1").Range("A1:C5")

The parameters of this method are:

  • Range: Required
  • Type: Optional
  • xlFillWithAll: Copy the contents and formats.
  • xlFillWithContents: Copy the contents.
  • xlFillWithFormats: Copy formats.

The Add2 method

This method is only available for Office 2013. The method applies to collections of Charts objects and returns a "Runtime" error when used with Sheets and WorkSheets objects. No more info on Microsoft.com except the syntax: expression .Add(Before, After,Count,NewLayout) Expression represents a Worksheet object. The parameters are identical to the Add method, simply replace the Type parameter with NewLayout. If NewLayout is set to True, the graphic is inserted using the new rules.

any more excel questions? check out our forum!
Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11
Removing " false" value in "IF" 10
Automatically transfer data from one sheet to another 22
Comparing two columns of names in Excel 8
Excel - To generate unique ID 4
converting number into words (Excel) 6

Excel