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

User · Accepted Answer

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

User · Answer

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

User · Answer

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  lt  gt     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     amp  inName  amp        amp  Err Description  vbOKOnly   unexpected error      Err Clear     GoTo the light End Function

User · Answer

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

User · Answer

This is a quick and simple add of a named tab to the current worksheet   Sheets Add Name    Tempo

User · Answer

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     amp  sheetName  amp     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

User · Answer

Kindly use this one liner   Sheets Add After  Sheets Sheets Count   Name    new sheet name

User · Answer

ThisWorkbook Sheets Add After  Sheets Sheets Count  ActiveSheet Name    XYZ     when you add a worksheet  anyway it ll be the active sheet

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

Examples related to excel

Examples related to vba

Examples related to worksheet