如何在所有 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

你能告诉我为什么吗?

推荐答案

试试这个:

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屋！