Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Creating Worksheets with a Macro.

Creating Worksheets with a Macro

by Allen Wyatt
(last updated October 8, 2011)

16

Excel lets you create new worksheets in a number of different ways. What if you want to create a new worksheet and name it all in one step? The easiest way to do this is with a macro. The following is an example of a macro that will ask for a name, and then create a worksheet and give that worksheet the name provided.

Sub AddNameNewSheet1()
    Dim Newname As String
    Newname = InputBox("Name for new worksheet?")
    If Newname <> "" Then
        Sheets.Add Type:=xlWorksheet
        ActiveSheet.Name = Newname
    End If
End Sub

This macro works fine, as long as the user enters a worksheet name that is "legal" by Excel standards. If the new name is not acceptable to Excel, the worksheet is still added, but it is not renamed as expected.

A more robust macro would anticipate possible errors in naming a worksheet. The following example code will add the worksheet, but keep asking for a worksheet name if an incorrect one is supplied.

Sub AddNameNewSheet2()
    Dim CurrentSheetName As String

'Remember where we started
'Not needed if you don't want to return
'to where you started but want to stay
'on the New Sheet

    CurrentSheetName = ActiveSheet.Name

'Add New Sheet
    Sheets.Add

'Make sure the name is valid
    On Error Resume Next

'Get the new name
     ActiveSheet.Name = InputBox("Name for new worksheet?")

'Keep asking for name if name is invalid
    Do Until Err.Number = 0
        Err.Clear
        ActiveSheet.Name = InputBox("Try Again!" _
          & vbCrLf & "Invalid Name or Name Already Exists" _
          & vbCrLf & "Please name the New Sheet")
    Loop
    On Error GoTo 0

'Go back to where you started
'Not needed if you don't want to return
'to where you started but want to stay
'on the New Sheet
    Sheets(CurrentSheetName).Select

End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2022) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Creating Worksheets with a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Checking Up On Numbers

When do you use digits in your prose and when do you spell out the numbers? Why not let Word help you make the decision? ...

Discover More

Converting Codes to Characters

Character codes are the numeric values used, by a computer, to signify various alphanumeric characters. You can use the CHAR ...

Discover More

Removing Entire Paragraphs from Your Document

If you need to get rid of a lot of paragraphs in a document, it's easy to do as long as the document relies on styles for ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Determining the RGB Value of a Color

Excel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value of ...

Discover More

Displaying the First Worksheet in a Macro

When creating macros, you often have to know how to display individual worksheets. VBA provides several ways you can display ...

Discover More

Finding the Path to the Desktop

Figuring out where Windows places certain items (such as the user's desktop) can be a bit frustrating. Fortunately, there are ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 1?

2017-02-08 20:29:09

Jess

Hello Alan,

Okay, I will post there once I tried the code you suggested. Haven't tried it yet since I've been busy lately. Thanks for the help also.

Jess


2017-02-08 17:03:55

Alan Elston

@Jess
I am posting around here at this Forum just now: ExcelFox ( as DocAElstein ) . If you still have problems you can start a Thread and ask in the Excel Help section, upload files, post larger codes easier. If no none else picks up the Thread then I will take a look.


2017-02-07 09:09:25

Alan Elston

Hi Jess
You should only use On Error Resume Next for an error you are predicting and know about. That is to say you are expecting the error and know exactly what is causing the error and that this is acceptable to you.
In this case the error that is occurring could explain the problem.
So it is usually important not to use On Error Resume Next to allow the code to continue on encountering an error until you know exactly what is causing the error.

It is impossible for me without seeing all your files to say exactly what the error could be.
But for example. If Excel does not like the name of the Worksheet name that you give here:_..
Worksheets.Add(After:=Worksheets.Item(Worksheets.Count)).Name = sName
_.. then it will error AFTER it has added a Worksheet. The worksheet is always initially added and given the next available worksheet name. That will be in English Excel, “Sheet1”, or “Sheet2”, or “Sheet3” etc, depending on the next Sheet number that you do not yet have. So using On Error Resume Next will mean that in such a case of an error, the worksheet may be made, but you will not get the new Worksheet given the name you wanted. You see that code line will work in the following order: Add a new worksheet( with a default name initially ), and then it will name it. So if it errored in trying to name it, then you will be left with the new worksheet with the default name, as it did that just before erroring.
_......


I cannot understand how my code can change the name of any existing Worksheet, ( other than the new one just created): - As far as I can see it will only name the newly added Worksheet ( if it can )

_.....

I would recommend never using an unqualified Range reference similar to this:
Range("B1").Value
I would always do some variation of these
Workbook(“MyFile.xlsm”).Worksheets("Master").Range("B1").Value
or
Dim WsM as Worksheet
Set WsM = ThisWorkbook.Worksheets(“Master”)
' .. then later in the code
WsM.Range("B1").Value

Using the first unqualified version, Range("B1"), means that you are relying on Excel to “guess” which worksheet you are wanting to go to reference the cell B1
_


So, I have had another go.
I am assuming the code is in a Workbook that has a worksheet “Master” in it, like this: http://imgur.com/me98K9q

When the code runs I am looking at some Active Worksheet. I use randomly cell A1 to copy and paste.
Cells B1, B2, B3 and B5 must also have something in them, as your original code is using tose values for the name of a new Workbook to create the new Workbook in necessary.
So my Active Worksheet, wherever that is looks like this: http://imgur.com/BPd9yzD
_..
So the first time the code runs it, creates a new Workbook with the full path that ends up like this:
C:UsersElstonDesktopcellB1cellB2CellB3 CellB5.xlsx
( So I had to have a Folder on my desktop with named cellB1, and in that Folder I had to have another Folder named cell B2

Finally I end up on that first run with a File named “CellB3 CellB5.xlsx” and it has a first worksheet named “cellB3 cellB4, cellB5”

On the second run, I assume I have the same worksheet active as before. So the second time around it does not make the Workbook, but opens the file with full path and name C:UsersElstonDesktopcellB1cellB2CellB3 CellB5.xlsx
It does not make a new worksheet either because the worksheet “cellB3 cellB4, cellB5” is there. You end up then with this: in workbook “CellB3 CellB5.xlsx” in the Worksheet “cellB3 cellB4, cellB5”: http://imgur.com/zppXfyi

Before the Third Run I deleted the worksheet “cellB3 cellB4, cellB5”. The code starts again with the same active worksheet as before. Once again it does not create the workbook, but it does this time create the new worksheet, which ends up looking like this: http://imgur.com/DqKLGBM



_.........................


I doubt if I can help much more at this distance without seeing your workbooks as I am having to guess a lot where and what various cell values are. As I mentioned if your code is erroring somewhere then that could be an indication of some string name that Excel does not like for a worksheet name.
I guess my last test runs were not doing exactly what you did, but the basic idea of the code below appears to work.

Hope that is some help

Alan

Code:

'
Sub JessTest2() ' http://excel.tips.net/T002022_Creating_Worksheets_with_a_Macro.html
Dim FilePath As String
Dim TestStr As String
Dim sname As String
'Dim sOld As String
Dim Aws As Worksheet
Set Aws = ActiveSheet
ActiveSheet.Range("A1").Copy

Dim WsM As Worksheet
Set WsM = ThisWorkbook.Worksheets("Master") ' I assume I have a worksheets "Master" in the Workbook that this code in it
Let sname = WsM.Range("B3") & " " & WsM.Range("B4") & ", " & WsM.Range("B5") '"TestName"
FilePath = "C:UsersElstonDesktop" & Aws.Range("B1").Value & "" & Aws.Range("B2").Value & "" & Aws.Range("B3").Value & " " & Aws.Range("B5").Value & ".xlsx": Debug.Print FilePath
'sOld = Sheets("Master").Range("B3") & " " & Sheets("Master").Range("B4").Value - 1 & ", " & Sheets("Master").Range("B5")
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
Workbooks.Add
ActiveSheet.Paste Destination:=ActiveSheet.Range("A1") 'I assume I am pasting to the active worksheet???
ActiveWorkbook.SaveAs Filename:=FilePath ' "D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
ActiveSheet.Name = sname
Else
Workbooks.Open Filename:=FilePath '"D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
'If Not Evaluate("=ISREF(" & "'[" & ActiveWorkbook.Name & "]" & sname & "'!A1)") Then ' This seems to error if the Workbook under test is not that of where this code is
If IsError(Evaluate("=ISREF(" & "'[" & ActiveWorkbook.Name & "]" & sname & "'!A1)")) Then
'On Error Resume Next'Do not do this!!!
Worksheets.Add(After:=Worksheets.Item(Worksheets.Count)).Name = sname
ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
Else
'ActiveWorkbook.Worksheets("" & sname & "").Move After:=ActiveWorkbook.Worksheets(Worksheets.Count)'Note: this clears the Clipboard!
ActiveWorkbook.Worksheets("" & sname & "").Activate
Dim Lr As Long: Let Lr = ActiveSheet.Range("A" & Rows.Count & "").End(xlUp).Row
If ActiveSheet.Range("A1").Value = "" Then Let Lr = 0 'last added row is 0 if not yet done, but Excel annoyingly alwas give 1
ActiveSheet.Paste Destination:=ActiveSheet.Range("A" & Lr + 1 & "")
End If
End If
End Sub


2017-02-07 05:52:34

Alan Elston

Hi Jess
I will have a go later at trying to do it a bit closer to what you are doing and let you know what happens
Alan


2017-02-06 21:57:12

Jess

Here is a screenshot of what is happened.

http://prnt.sc/e5grcr

The one on the left was the data when the workbook (april) was created. When new days are added the first sheet becomes 'Sheet1' and the next sheet is the name of the new day but no data is transferred. 'Sheet1' doesn't change also so I assume it is still day 1

I am confused why it won't work


2017-02-06 21:50:41

Jess

Hello Alan,

Actually the codes I used were a compilation of the things I saw in the internet and needed. So am not sure on some of the lines but I did test it out by removing the On Error Resume Next and it did result to an error which stopped the macro.

I used the one you posted but verified it a bit like the filename equal to a cell so that it could be changed.

It can create a new workbook if it doesn't exist. The problem, however, is still the same as before. If the workbook exists already, the first sheet is changed to Sheet1 and the next sheet has the new name but the new sheet doesn't have the data transferred.

I am actually trying to make a daily logbook. The workbook is the month and data for the day are saved and named that day. The next day is saved in a separate sheet but still keeping the previous days.

I'm just not sure what code to use.

Jess


2017-02-06 10:32:31

Alan Elston

Hi Jess
I think I may see the problem.

A small point first: I see you have one occurrence of On Error Resume Next just before you add a worksheet. I would only ever use On Error Resume Next for an error I am expecting, and then I would use On Error GoTo 0 to turn it off , as you do earlier on the code. I see no reason for your second use of it just before you add a worksheet... or maybe I do _......
_..because....
HAVING SAID that I can probably guess why you may have done that. - The line after it would error if the Worksheet already existed. That would likely of happened.
The problem is that my idea of evaluating of If the Reference to the Worksheet exists is defaulting to looking for that reference in the Workbook in which the code is in, and not to the active Workbook.
_...
So: The reference checked for needs to include the Workbook testing for. In fact, with hindsight, it is probably a good idea always to include the workbook name in the reference checked for. It does no harm to have that included even if it is not needed. In your case it is needed
Further, it appears that when the reference does not exist for such a reference, it errors rather than returning False. I am not quite sure why that is. But the solution for now is to test that for an error, rather than for False
_...

So
I put these strings in the first two cells of the current Active Worksheet
cellA1 cellB1
I made a File “Jess1.xlsx” on my desktop
I ran a modified version of your code: It seems to work, - It does this:

The first two cells from the Active Worksheet are copied to the clipboard
If the Workbook, “Jess1.xlsx”, exists, that is to say the directory "C:UsersElstonDesktopJess1.xlsx" exists , then that Workbook, “Jess1.xlsx”, is opened.
__ If the Worksheet "TestName" does not exist in that workbook then it is made and the first two cells are filled from the clipboard
__ Otherwise the Clipboard is pasted to the next free row in the existing worksheet “TestName”


Alan

Code:
Sub JessTest() ' http://excel.tips.net/T002022_Creating_Worksheets_with_a_Macro.html
Dim FilePath As String
Dim TestStr As String
Dim sname As String
'Dim sOld As String
ActiveSheet.Range("A1:B1").Copy
Let sname = "TestName"
FilePath = "C:UsersElstonDesktopJess1.xlsx" '"D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
'sOld = Sheets("Master").Range("B3") & " " & Sheets("Master").Range("B4").Value - 1 & ", " & Sheets("Master").Range("B5")
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
Workbooks.Add
ActiveSheet.Paste Destination:=Range("A1")
ActiveWorkbook.SaveAs Filename:="D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
ActiveSheet.Name = sname
Else
Workbooks.Open Filename:=FilePath '"D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
'If Not Evaluate("=ISREF(" & "'[" & ActiveWorkbook.Name & "]" & sname & "'!A1)") Then ' This seems to error if the Workbook under test is not that of where this code is
If IsError(Evaluate("=ISREF(" & "'[" & ActiveWorkbook.Name & "]" & sname & "'!A1)")) Then
Worksheets.Add(After:=Worksheets.Item(Worksheets.Count)).Name = sname
ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
Else
'ActiveWorkbook.Worksheets("" & sname & "").Move After:=ActiveWorkbook.Worksheets(Worksheets.Count)'Note: this clears the Clipboard!
ActiveWorkbook.Worksheets("" & sname & "").Activate
Dim Lr As Long: Let Lr = ActiveSheet.Range("A" & Rows.Count & "").End(xlUp).Row
If ActiveSheet.Range("A1").Value = "" Then Let Lr = 0 'last added row is 0 if not yet done, but Excel annoyingly alwas give 1
ActiveSheet.Paste Destination:=ActiveSheet.Range("A" & Lr + 1 & "")
End If
End If
End Sub


2017-02-06 00:03:34

Jess

I forgot to add that the excel version I am using is 2016. I just recently saw that this is for earlier versions.


2017-02-05 23:58:25

Jess

Hello Alan,

I tried it but still got the same result. When the workbook exists, either I replaced the existing worksheet. Create a new sheet with the new sheet named properly but the worksheet is blank.

I included below the code that I was using.

Jess

Sub Button2_Click()

Dim FilePath As String
Dim TestStr As String
Dim sName As String
Dim sOld As String

ActiveSheet.Range("A1:Y27").Copy
ActiveSheet.Range("A1:Y27").Copy
sName = Sheets("Master").Range("B3") & " " & Sheets("Master").Range("B4") & ", " & Sheets("Master").Range("B5")
FilePath = "D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
sOld = Sheets("Master").Range("B3") & " " & Sheets("Master").Range("B4").Value - 1 & ", " & Sheets("Master").Range("B5")
TestStr = ""

On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0

If TestStr = "" Then
Workbooks.Add
ActiveSheet.Paste Destination:=Range("A1")
ActiveWorkbook.SaveAs Filename:= _
"D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"
ActiveSheet.Name = sName

Else

Workbooks.Open "D:DesktopBook" & Range("B1").Value & "" & Range("B2").Value & "" & Range("B3").Value & " " & Range("B5").Value & ".xlsx"

If Not Evaluate("=ISREF(" & "'" & sName & "'!A1)") Then

On Error Resume Next
Worksheets.Add(After:=Worksheets.Item(Worksheets.Count)).Name = sName
ActiveSheet.Paste

Else

ThisWorkbook.Worksheets("" & sName & "").Move After:=ThisWorkbook.Worksheets(Worksheets.Count)
Worksheets("" & sName & "").Activate

End If

End If

End Sub


2017-02-03 09:51:42

Alan Elston

Hi Jess
You may be able to adapt this:
It works on the Active Workbook.
It checks for a the existence of a Worksheet.
If it does not exist it makes it
If it does exist , it moves it to the last tab – you could modify this last bit so that it made a worksheet with a different name.

Alan



' http://excel.tips.net/T002022_Creating_Worksheets_with_a_Macro.html
Sub WonkSheetOnlyIfItDoesNotExist6() 'Does work to add Woorksheet if it does not exist, if it does exist it moves it to the end
Dim Check As String: Let Check = "Plopy" & Date ' Change name to suit
If Not Evaluate("=ISREF(" & "'" & Check & "'!A1)") Then 'Check to see if the sheet is there by seeing if the reference to cell A1 in that sheet doesn't exist. If it is true that it does not exist, Then (Note: the extra ' are just needed incase your Worksheet name has a space in it. Excel uses that so as not to confuse the space with that between arguments. It does not look for a the ' in the name)
Worksheets.Add(After:=Worksheets.Item(Worksheets.Count)).Name = Check
Else
ThisWorkbook.Worksheets("" & Check & "").Move After:=ThisWorkbook.Worksheets(Worksheets.Count) 'Otherwise If the sheet is there it could be anywhere so we put it after last , ( and activate it so next lines will work )
Worksheets("" & Check & "").Activate
End If
End Sub


2017-02-02 20:51:09

Jess

Hello,

I am trying to make a macro that adds a sheet in an existing workbook.

My macro checks first if the workbook exists, if not it will create it then transfer the data from Input workbook to the new workbook.

This part works fine, the problem is when the workbook already exists. The data replaces the existing worksheet in the new workbook instead of creating a new one.


2016-12-16 05:59:39

Alan Elston

Hi QLO,
I always forget the syntax with these things. They are the sort of things that are very easy to get by doing a macro recording whilst doing it manually. The code you then get is very easy to understand and modify. You end up with something like this:
Sub NewSheet()
Dim wsOld As Worksheet, wsNew As Worksheet
Set wsOld = ThisWorkbook.Worksheets("OldSheet") ' - Change to suit your current Worksheet Name
wsOld.Copy After:=ThisWorkbook.Worksheets.Item(ThisWorkbook.Worksheets.Count) ' Copy Place after last Worksheet Item Number (Count of Worksheets is equal to last tab counting from left which is the last Worksheet Item Number ))
Set wsNew = ActiveSheet
Let wsNew.Name = "NewSheet" ' - Change to suit the new name you want
End Sub


2016-12-16 05:34:55

Barry

@QLO

Just substitute:
Worksheets("SourceSheetName").Copy instead of Sheets.Add

Change "SourceSheetName" to the name of the original sheet that you want to be copied.


2016-12-16 03:32:55

QLO

Hi,

I know the article is old.
But i have a question.

If i use the code above, but want it to copy/paste a sheet every time, so it will be the same data in the new sheet but with an new sheet name, is this possible to include in the macro?


2016-08-01 09:19:10

Valentin

Excellent! Works great and has helped me a lot. Thanks.


2016-06-01 05:52:08

praveen kumar N

I like this code. this is helps lot to me . now I am starting my career in coding thank you so much.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.