Creating Worksheets with a Macro

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

@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.

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

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

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

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

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

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

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

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

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.

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

@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.

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?

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

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