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

by

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

More Related Contents:

  1. excel vba compare & copy worksheets from 2 excel files
  2. VBA code doesn’t run when cell is changed by a formula
  3. Copy sheet and get resulting sheet object?
  4. Reducing WithEvent declarations and subs with VBA and ActiveX
  5. How can I loop through a subset of worksheets?
  6. Excel VBA For Each Worksheet Loop
  7. Sequence of numbers based on start and end value of 2 cells [closed]
  8. How to insert a picture into Excel at a specified cell position with VBA
  9. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  10. How can I create a calendar input in VBA Excel?
  11. Using Google Chrome in Selenium VBA (Installation Steps)
  12. Dir() function not working in Mac Excel 2011 VBA
  13. Merge values of column B based on common values on column A
  14. How to use workbook.saveas with automatic Overwrite
  15. How to import a table from web page (with “div class”) to excel?
  16. Concatenate cell data into another data if values matches
  17. Iterating through populated rows
  18. Using VBA to detect which decimal sign the computer is using
  19. Loop through each row of a range in Excel
  20. Fill formula down till last row in column
  21. Outputting Excel rows to a series of text files
  22. How to create a range from two ranges in VBA?
  23. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  24. If string contains word from list, return value adjacent to list value
  25. excel vba freeze pane without select
  26. Converting a string representation of a constant into a constant?
  27. VBA: Modify chart data range
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment