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?
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