Second, you need to write a code using this function and that code should also check if the name cell is blank or not. Function SheetCheck(sheet_name As String) As Boolean Let me put it in steps two steps:įirst, you need to write an Excel User Defined Function to check if a sheet with the same name already exists or not. If both conditions are fulfilled only then it should add a new sheet. In that case, you need to write a code that can verify if the sheet with the same name already exists or not and the cell from where you want to take the sheet name is blank or not. Sheet_name = Sheets("mySheet").Range("A1:A7").Cells(i, 1).Valueīut with the above code, there could be a chance that the sheet name you want to add already exists or you have a blank cell in the name range.
After that, it loops to add sheets according to the count from the range and use values from the range name the sheet while adding it. The following code counts rows from the range A1:A7. Add Multiple Sheets and use Names from a Range In the above code, Sheet.Count returns the count of the sheets that you have in the workbook, and as you have defined the after argument it adds the new sheet after the last sheet in the workbook. So, for this, you need to know how many sheets there in the workbook are so that you can add a new sheet at the end. To add a new sheet in the end you need to write the code in a different way. Add a New Sheet at End (After the Last Sheet) In this way, it will always add the new sheet at the beginning. In the above code, you have used the sheet number (1) that tells VBA to add the sheet before the sheet which is on the first position in all the worksheets. So basically, what we are going to do is we’re going to specify the sheet number instead of the sheet name. Add a New Sheet at Beginningīy using the before argument using you can also add a sheet at the beginning of the sheets that you have in the workbook. So, when you run this code it adds two sheets one is before and one is after the “mySheet”.
Now in the above code, you have two lines of code where you have used before and after an argument in the Sheet.Add method.
Add a Sheet After/Before a Specific SheetĪs these arguments are already there in the Sheets.Add where you can specify the sheet to add a new sheet before or after it. In the above code, cell A1 is used to get the name for the new sheet. You can also take the value to use as the sheet’s name from a cell. In the above code, we have used the name object ( LINK) which helps you to specify the name of a sheet. If you want to rename the sheet after adding it, you can use the following code: Sub AddNewSheetswithNameExample1() Now the count of the sheets that you have defined is 5, so when you run this code it instantly adds the five new sheets in the workbook. To add multiple sheets in one go, you just need to define the COUNT argument with the number of sheets you want to add. Sub SheetAddExample2()Īs you are already in the active workbook you can use the below code as well. Here’s one more way to write this, check out the below code. This code tells Excel to add a sheet in the active workbook, but as you don’t have any argument it will use the default values and add one worksheet(xlWorksheet) before the active sheet. To add a single sheet, you can use the below code, where you didn’t specify any argument.
Different Ways to Add New Sheets in a Workbook using a VBA Codeīelow you have different ways to add a new sheet in a workbook: 1.