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.
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.
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: 
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Do you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. You ...
Discover MoreMacros give you a great deal of control over creating, finding, renaming, and deleting files. This tip focuses on this last ...
Discover MoreWorkbooks can contain macros, or not. It is entirely up to you whether they do or not, but at some future time you might want ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
@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
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
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
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
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
@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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments