使用Excel VBA创建新工作表失效仅激活最后一张表如何解决

问题原因 #

• 工作簿结构处于保护状态，限制了新增、重命名工作表的操作，Sheets.Add方法执行失败无显性报错时，就会出现仅激活最后一个工作表的表现
• 代码存放在个人宏工作簿（PERSONAL.XLSB）中，ThisWorkbook默认指向隐藏的个人宏工作簿，新工作表被创建到了隐藏文件中，当前可见工作簿无变化
• 当前工作簿内已存在名为Tempo的工作表，重命名步骤触发命名冲突错误，代码提前终止，新增的空白工作表未被重命名就被跳过（部分场景下新增操作也会因为错误回滚）
• 宏安全级别设置过高，代码未获得操作工作表的完整权限，仅执行了激活最后一个工作表的前置逻辑

解决方法 #

• 解除工作簿结构保护：点击顶部「审阅」选项卡→选择「保护工作簿」，如果选项处于勾选状态，输入对应密码解除保护后再运行代码
• 修正工作簿指向：如果代码存放在个人宏工作簿，将代码中的ThisWorkbook替换为ActiveWorkbook，指定操作当前打开的可见工作簿，或者直接把代码移动到目标工作簿的模块中
• 避免重名冲突：先删除当前工作簿中已有的Tempo工作表，或者修改代码中的目标工作表名称
• 调整宏安全设置：点击「文件」→「选项」→「信任中心」→「信任中心设置」→「宏设置」，选择「启用所有宏」或「启用已签名的宏」，重启Excel后再运行代码

优化后可定位问题的代码 #

Private Sub CreateSheet()
    Dim ws As Worksheet
    Dim targetWb As Workbook
    ' 指定操作当前激活的工作簿，避免指向错误
    Set targetWb = ActiveWorkbook
    
    ' 预检查同名工作表
    On Error Resume Next
    Set ws = targetWb.Sheets("Tempo")
    On Error GoTo 0
    If Not ws Is Nothing Then
        MsgBox "已存在名为Tempo的工作表，无法重复创建", vbExclamation
        Exit Sub
    End If
    
    ' 执行创建逻辑
    Set ws = targetWb.Sheets.Add(After:=targetWb.Sheets(targetWb.Sheets.Count))
    ws.Name = "Tempo"
    MsgBox "工作表""Tempo""创建成功", vbInformation
End Sub

内容的提问来源于stack exchange，提问作者avoid progress