Excel VBA：批量重命名表 [英] Excel VBA: Batch renaming sheets

问题描述

有什么办法可以在VBA中批量重新命名表：

  sheet（array（1，2，3））。name = array（hep，hey，heppa！）
  

命名表1，2，3为hep，hey和heppa！

解决方案

• 显然它不能直接工作


• 还有一些实验与 SelectedSheets 没有导致任何

这是尽可能接近，有人可能会找到一种方法来跳过循环

[以标准的方式更新我将这样做下面包含错误处理 - 我没有真正尝试过设置这样的页面集合]

正常代码

  Sub Normal（）
 Dim strShtOld（）
 Dim strShtNew（）
 Dim sht As Worksheet $ 
 strShtOld = Array（Sheet1，Sheeta2，Sheet3）
 On Error Resume Next 
对于lngSht = LBound（strShtOld）到UBound（strShtOld）
设置ws = Nothing 
设置ws = Sheets（strShtOld（lngSht））
如果不是ws Is Nothing Then ws.Name = strShtNew（lngSht）
下一页lngSht 
 End Sub 
  

为什么批量重命名，好奇或做你有这么大的重命名，你关心代码运行时？

Array Effort

  Sub ArrayEx（）
 Dim varShts 
 Dim varSht 
 Dim strArray（）
 strArray = Array（hep，hey，heppa！）
设置varShts = Sheets（Array（Sheet1 ，Sheet2，Sheet3））
对于varSht = 1到varShts.Count 
 varShts（varSht）.Name = strArray（varSht  -  1）
下一个
 End Sub 
  

Is there any way to batch rename sheets in VBA:

Something like:

sheets(array(1, 2, 3)).name = array("hep", "hey", "heppa!")

naming sheets 1, 2, 3 as "hep", "hey" and "heppa!"

解决方案

• Clearly it doesn't work directly
• And some experimentation with SelectedSheets didn't lead anywere

This is as close as I could get it, someone else may find a method to skip a loop

[Updated with the standard way I would do this below including error handling - I hadn't actually tried setting a collection of sheets like this before]

Normal Code

Sub Normal()
    Dim strShtOld()
    Dim strShtNew()
    Dim sht As Worksheet
    Dim lngSht As Long
    strShtNew = Array("hep", "hey", "heppa!")
    strShtOld = Array("Sheet1", "Sheeta2", "Sheet3")
    On Error Resume Next
    For lngSht = LBound(strShtOld) To UBound(strShtOld)
        Set ws = Nothing
        Set ws = Sheets(strShtOld(lngSht))
        If Not ws Is Nothing Then ws.Name = strShtNew(lngSht)
    Next lngSht
End Sub

Why the batch rename, curiousity or do do you have such a large amount of renaming to do that you are concerned with code runtime?

Array Effort

    Sub ArrayEx()
    Dim varShts
    Dim varSht
    Dim strArray()
    strArray = Array("hep", "hey", "heppa!")
    Set varShts = Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    For varSht = 1 To varShts.Count
        varShts(varSht).Name = strArray(varSht - 1)
    Next
    End Sub

这篇关于Excel VBA：批量重命名表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！