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.
Excel
- Vba add sheet
- Excel vba add sheet
- Add sheet vba
- Excel vba add command button programmatically
- What is the VBA code to select last sheet in workbook > Guide
- Copy Worksheet to another Workbook (unopened) [solved] > Forum - Excel
- Create new sheet based on cell value: Excel, macro > Guide
- VBA Code to Copy Worksheet multiple times and rename based on list [solved] > Forum - Excel
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Copy data from one excel sheet to another: automatically
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to take screenshot in Excel: shortcut, sheet, cell
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How many IF statements can you nest in Excel
- How to use Excel color cell if formula
- How to display multiple columns in a validation list
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- How to run macro when data entered in a cell
- Check if a value exists in an array VBA
- Search and find using VBA in Excel
- VBA select case like: operator, string, statement
- VBA Excel color codes: index number, list
- How to insert file path in excel: cell, sheet
- How to disable auto recover in Excel
- How to enable VBA in Excel: Mac, Office 365
- How combine IF function, SEARCH and ISERROR in Excel
- How to enter multiple lines in a single Excel cell
- How to insert GIF in Excel: 365, sheet, VBA
- How to count names in Excel: formula, using COUNTIF
- Open and convert an Excel file in Notepad
- Repeat rows in Excel: based on cell value, VBA
- How to automatically transfer data between sheets in Excel
- How to manipulate data in Excel: VBA
- How to copy a Macro into a blank cell
- Insert a hyperlink in Excel: with text, to another tab
- Recover Excel file: previous version
- Run macro on opening: worksheet, workbook
- How to transfer data from one Excel sheet to another?
- Most useful Excel formulas: for data analysis
- Recalculate Excel Workbook Before Saving
- How to apply if function in Excel: with dates, with text
- Select the default number of processors in Excel
- Insert picture in Excel: cell, shortcut, using formula
- Unlock password protected Excel file: Macro
- Excel functions in French
- How to create a cascading combo box: Excel, VBA
- How to create calculator in Excel VBA
- Excel export data: from one sheet to another
- How to add a number of days to a date in Excel
- How to fill multiple Excel sheets from master sheet
- How to copy data to another workbook: using VBA
- Transfer Excel data from one sheet to another: VBA
- Excel VBA add command button programatically
- How to change a column to numeric in Excel?
- Mark sheet grade formula in Excel: template
- How to connect VB 6.0 with MS Access
- How to remove leading apostrophe in Excel
- How to create UserForm: in Excel, VBA
- Select empty cell in Excel: VBA
- Excel send value to another cell
- Using VBA to find last non empty row: in column, in table
- How to show or hide formula bar in Excel: VBA, shortcut
- How to change enter key function in Excel
- How to use Excel auto numbering formula
- Conditional formatting with dates: in Excel
- How to calculate VAT in Excel: formula
- Transfer a worksheet to another Excel workbook: without VBA
- Delete duplicates in Excel: column, formula
- Send email with attachment Excel: VBA, macros
- How to Insert a Transparent Image into an Excel Document
- Compare two Excel sheets: and combine data
- Copy data from one Excel workbook to another
- Auto generate serial number in Excel: VBA, formula
- How to generate email notifications for Excel updates
- How to create a drop-down List in Excel
- Split a workbook into individual files in Excel
- How to insert blank rows using macros in Excel
- Run Macros in Excel: online, shortcuts, VBA
- How to clear formatting in Excel
- Insert an image into an Excel comment box
- Credit summation formula
- How to insert multiple rows in Excel with a macro?
- How to change Excel date format
- How to create a timer in Excel VBA
- How to use conditional formatting in Excel
- How to count occurrences of characters and numbers in Excel
- Shortcuts to insert in Excel
- How to copy data to multiple worksheets in Excel
- Excel IF, AND, OR, and NOT functions
- Create a new workbook and copy data in Excel via a Macro
- Update dates in Excel spreadsheet: each year, formula
- How to change row color in Excel VBA based on value