在宏中重命名工作表而不重命名前四个工作表 [英] Renaming Sheets in Macro without renaming first four sheets

问题描述

我知道如何制作 marcos，但在学校期间，他从未教过我与它们有关的所有事情，主要是关于 Dim.我的问题是如何制作一个 marco 来重命名我所有的工作表，除了前四个.

I know how to make marcos but during school he never taught me everything to do with them, mainly with the Dim. My questions is how to I make a marco that will rename all my sheets expect for the first four.

Sub RenameSheet()

    Dim rs As Worksheet

    For Each rs In Sheets
        rs.Name = rs.Range("D5")
    Next rs

End Sub

适用于每张纸，但我不想重命名每张纸.我的前四个是 Documentation、Summarry、RONATemplate、KaycanTemplate.我想离开的是.我真的不能只是将这些名称放在单元格 D5 中以使其在它的模板中工作，它会弄乱我的其他 marcos.

Works for every sheet but I dont want to be renaming every sheet. My first four are Documentation, Summarry, RONATemplate, KaycanTemplate. Which I want to leave has is. I cant really just put those names in cell D5 to make it work where its a template, and it will mess up my other marcos.

推荐答案

第一个选项是使用基于工作表索引/编号迭代的不同类型的循环.以下是仅针对 Worksheets Collection 运行的代码:

First option is to use different kind of loop which iterates based on sheet index/number. Here is the code running only for Worksheets Collection:

Sub RenameSheet()

    Dim rs As Long

    For rs = 5 To Worksheets.Count
        Worksheets(rs).Name = Worksheets(rs).Range("D5")
    Next rs

End Sub

您的循环从工作表的第 5 个开始，一直运行到最后一个.

Your loop starts as of 5th of worksheet and runs until the last one.

另一种选择是排除具有您在问题中提到的名称的所有工作表.在这种情况下，您可以运行此宏:

The other option is to exclude all sheets with names which you have mentioned in your question. In that situation you could run this macro:

Sub RenameSheet()

    Dim rs As Worksheet

    For Each rs In Sheets
        if rs.name <> "Summary" And rs.Name <> "RONATemplate" and rs.Name <> "KeycanTemplate" Then
            rs.Name = rs.Range("D5")
        end if
    Next rs

End Sub

但是，请记住所有条件检查，如 rs.Name <>摘要" 区分大小写，因此您需要在代码中放置适当的名称，包括大写和小写.或者您可以使用 UCase 函数与大写名称进行比较，例如:

However, keep in mind that all conditional checks like rs.Name <> "Summary" are case sensitive therefore you need to put appropriate names within code including upper- and lower- cases. Or you could use UCase function to compare to capitalized names like:

if UCase(rs.Name) <> "SUMMARY" And UCase(rs.Name) <> "RONATEMPLATE" And Ucase(rs.Name) <> "KEYCANTEMPLATE" Then

我建议使用第二种改进的程序.如果您更改工作表的顺序(例如，将第一张工作表移到第 6 个位置)，您将在第一个 For i=1 循环中获得意外结果.运行第二种类型的循环/子程序不存在这样的问题.

I would suggest to use second type of improved procedure. If you change the order of your sheets (e.g. move first sheet into 6th position) you will get unexpected results running first For i=1 loop. There is not such problem running second type of loop/subroutine.

这篇关于在宏中重命名工作表而不重命名前四个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！