How to add a named sheet at the end of all Excel sheets?

Question

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working:

Private Sub CreateSheet()
    Dim ws As Worksheet
    ws.Name = "Tempo"
    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
End Sub

Can you please let me know why?

Because you haven't created `ws` prior to assigning a `.Name`. Don't get confused: instantiate your variable first before attempting to access its properties. — WGS, Dec 20 '13 at 06:41

L42 · Accepted Answer · 2016-04-06T00:43:09.423

167

Try this:

Private Sub CreateSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Tempo"
End Sub

Or use a With clause to avoid repeatedly calling out your object

Private Sub CreateSheet()
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "Tempo"
    End With
End Sub

Above can be further simplified if you don't need to call out on the same worksheet in the rest of the code.

Sub CreateSheet()
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
    End With
End Sub

edited Apr 06 '16 at 00:43

answered Dec 20 '13 at 06:39

L42

19,427
11
44
68

why what's wrong? you are not creating the sheet in the same workbook? – L42 Dec 20 '13 at 06:48
11

ThisWorkbook is the workbook that hosts the running macro. ActiveWorkbook is the the workbook the user is working (said in plain terms). WorkBooks("somename") addresses a specific open workbook – Massimo Fuccillo Dec 20 '13 at 09:16
1

Nice, but what happens if sheet already exist, Would it throw an error ? – Ch32k0 Aug 26 '16 at 21:20
1

@Ch32k0 Yes. If the same name already exist or if you use an invalid name. – L42 Sep 02 '16 at 03:21
@L42 Oh, I experimented with this and it actualy does, thanks for the feedback – Ch32k0 Sep 16 '16 at 18:57

score 42 · Answer 2 · answered Jan 03 '16 at 05:27

42

Kindly use this one liner:

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name"

answered Jan 03 '16 at 05:27

Amar

429
4
6

1

Sorry, but the accepted answer looks better. This one does not explicitly indicate `ThisWorkbook` or `ActiveWorkbook`. Also, the new worksheet object here is not assigned to a variable, while afterwards almost always you will want to use it for putting data into the sheet. – ZygD Feb 21 '19 at 08:50
1

I think this answer does the trick best. It is not specified that the sheet should be assigned to a variable. He know's it's name obviously since it's specified in the code. – IronWilliamCash Apr 08 '19 at 02:41

Saptarshi · Answer 3 · 2015-09-14T08:01:50.883

7

ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "XYZ"

(when you add a worksheet, anyway it'll be the active sheet)

edited Sep 14 '15 at 08:01

answered Sep 14 '15 at 07:56

Saptarshi

125
2
3

score 5 · Answer 4 · edited Oct 07 '14 at 13:13

Try this:

Public Enum iSide
iBefore
iAfter
End Enum
Private Function addSheet(ByRef inWB As Workbook, ByVal inBeforeOrAfter As iSide, ByRef inNamePrefix As String, ByVal inName As String) As Worksheet
    On Error GoTo the_dark

    Dim wsSheet As Worksheet
    Dim bFoundWS As Boolean
    bFoundWS = False
    If inNamePrefix <> "" Then
        Set wsSheet = findWS(inWB, inNamePrefix, bFoundWS)
    End If

    If inBeforeOrAfter = iAfter Then
        If wsSheet Is Nothing Or bFoundWS = False Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = inName
        Else
            Worksheets.Add(After:=wsSheet).Name = inName
        End If
    Else
        If wsSheet Is Nothing Or bFoundWS = False Then
            Worksheets.Add(Before:=Worksheets(1)).Name = inName
        Else
            Worksheets.Add(Before:=wsSheet).Name = inName
        End If
    End If

    Set addSheet = findWS(inWB, inName, bFoundWS)         ' just to confirm it exists and gets it handle

    the_light:
    Exit Function
    the_dark:
    MsgBox "addSheet: " & inName & ": " & Err.Description, vbOKOnly, "unexpected error"
    Err.Clear
    GoTo the_light
End Function

Ivan Tokarev · Answer 5 · 2016-03-23T10:27:09.710

Try to use:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"

If you want to check whether a sheet with the same name already exists, you can create a function:

Function funcCreateList(argCreateList)
    For Each Worksheet In ThisWorkbook.Worksheets
        If argCreateList = Worksheet.Name Then
            Exit Function ' if found - exit function
        End If
    Next Worksheet
    Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = argCreateList
End Function

When the function is created, you can call it from your main Sub, e.g.:

Sub main

    funcCreateList "MySheet"

Exit Sub

score 4 · Answer 6 · answered Nov 15 '16 at 19:04

4

Try switching the order of your code. You must create the worksheet first in order to name it.

Private Sub CreateSheet()
    Dim ws As Worksheet
    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = "Tempo"
End Sub

thanks,

answered Nov 15 '16 at 19:04

Developer

761
7
12

score 3 · Answer 7 · answered Oct 17 '17 at 22:19

This will give you the option to:

Overwrite or Preserve a tab that has the same name.
Place the sheet at End of all tabs or Next to the current tab.
Select your New sheet or the Active one.

Call CreateWorksheet("New", False, False, False)


Sub CreateWorksheet(sheetName, preserveOldSheet, isLastSheet, selectActiveSheet)
  activeSheetNumber = Sheets(ActiveSheet.Name).Index

  If (Evaluate("ISREF('" & sheetName & "'!A1)")) Then 'Does sheet exist?
    If (preserveOldSheet) Then
      MsgBox ("Can not create sheet " + sheetName + ". This sheet exist.")
      Exit Sub
    End If
      Application.DisplayAlerts = False
      Worksheets(sheetName).Delete
    End If

    If (isLastSheet) Then
      Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetName 'Place sheet at the end.
    Else 'Place sheet after the active sheet.
      Sheets.Add(After:=Sheets(activeSheetNumber)).Name = sheetName
    End If

    If (selectActiveSheet) Then
      Sheets(activeSheetNumber).Activate
    End If

End Sub

score -2 · Answer 8 · edited Oct 22 '15 at 19:05

-2

This is a quick and simple add of a named tab to the current worksheet:

Sheets.Add.Name = "Tempo"

edited Oct 22 '15 at 19:05

rink.attendant.6

44,500
61
101
156

answered Oct 22 '15 at 19:04

Jan

421
3
13

How to add a named sheet at the end of all Excel sheets?

8 Answers8

Linked

Related