如何在所有excel表的末尾添加命名表 [英] How to Add a Named Sheet at the end of all excel sheets

问题描述

我正尝试在所有现有表格的末尾添加名为Temp的Excel表单，但此代码不起作用

I am trying to Add an Excel sheet with Name "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? thanks

推荐答案

尝试这样：

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

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