如何在所有Excel工作表的末尾添加命名工作表? [英] How to add a named sheet at the end of all Excel sheets?

问题描述

我试图在所有现有工作表的末尾添加一个名为"Temp"的Excel工作表，但是此代码不起作用:

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?

推荐答案

尝试一下:

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

或使用With子句避免重复调用对象

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

这篇关于如何在所有Excel工作表的末尾添加命名工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！