精华内容
下载资源
问答
  • SUB 批量增加命名工作表() For L = 1 To 100 Sheets.Add(, Sheets("发票表头")).Name = "发票表" & i NEXT END SUB SUB 批量删除工作表() Application.DisplayAlerts = 0 ‘’‘关闭提示 On Error Resume ...
    SUB 批量增加命名工作表()
     For L = 1 To 100
    Sheets.Add(, Sheets("发票表头")).Name = "发票表" & i
    NEXT
    
    END SUB
    
    SUB 批量删除工作表()
    Application.DisplayAlerts = 0  ‘’‘关闭提示
    On Error Resume Next
    X = Worksheets.Count
    For i = 1 To X 
    
    Worksheets("发票表" & i).Delete
    Next
    Application.DisplayAlerts = 1
    MsgBox ("完成" & X - 3 & "个工作表删除!")
    end SUB
    
    展开全文
  • 根据sheet1所列名称,添加并重命名新的工作表。 二、实现 1.按照从前往后的顺序 Sub cre_ren_sheets() Dim num% /* 定义为integer*/ num = Application.WorksheetFunction.CountA(Sheet1.Range("A:A")) /* num...
    一、需求

    根据sheet1所列名称,添加并重命名新的工作表。
    在这里插入图片描述

    二、实现
    1.按照从前往后的顺序
    Sub cre_ren_sheets()
    	Dim num% 
    	/* 定义为integer*/
    	num = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
    	/* num是非空单元格数*/
    	
    	For i = 1 To num
    	    Sheets.Add after:=ActiveSheet
    	    Sheets(i + 1).Select
    	    Sheets(i + 1).Name = Sheet1.Cells(i, 1)
    	Next i
    End Sub
    

    在这里插入图片描述

    2.按照从后往前的顺序
    Sub cre_ren_sheets()
    	Dim num%
    	num = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
    	
    	For i = 1 To num
    	    Sheets.Add
    	    Sheets(1).Name = Sheet1.Cells(i, 1)
    	    /* Sheet1=Sheets(i+1), sheet1随着新工作表的建立被往后挤,序列数随之增大*/
    	Next i
    End Sub
    
    

    在这里插入图片描述

    三、注意事项
    1.代码中的索引号和新建sheet默认命名不一定一致,代码按照顺序,工作簿只是名称。

    在这里插入图片描述

    2.工作表的命名要求

    在这里插入图片描述

    展开全文
  • vba工作表重命名Renaming of worksheets can be done manually, but it will look like a massive task if there are a lot of worksheets to be renamed and when we need to rename it based on some conditions ...

    vba工作表重命名

    Renaming of worksheets can be done manually, but it will look like a massive task if there are a lot of worksheets to be renamed and when we need to rename it based on some conditions or values.

    重命名工作表可以手动完成,但是如果要重命名许多工作表以及需要根据某些条件或值重命名工作表,这将是一项艰巨的任务。

    Today, I'm going to show how we can actually rename the worksheets based on conditions by writing some VBA codes.

    今天,我将展示如何通过编写一些VBA代码,根据条件实际重命名工作表。

    Create the fundamental

    创造基础

    Renaming the worksheet can be as easy as looking for a fixed value and then renaming it accordingly with another fixed value.

    重命名工作表就像查找一个固定值,然后相应地使用另一个固定值重命名一样容易。

    Worksheets("Sheet1").Name = "New Sheet Name" 
    

    When we are trying to rename many worksheets, we can repeat to call the codes with minimal changes:

    当我们尝试重命名许多工作表时,我们可以重复执行以最少的更改来调用代码:

    Worksheets("Sheet1").Name = "New Sheet Name"
    Worksheets("Sheet2").Name = "New Sheet Name (2)" 
    

    Note: You must be careful when you trying to rename your worksheet:

    注意:尝试重命名工作表时,必须小心:

    1) Make sure the worksheet name you're referring to exists in the current worksheet

    1 )确保您要引用的工作表名称在当前工作表中存在

    2) Make sure the new worksheet name doesn't exist in the current worksheet

    2 )确保新工作表名称在当前工作表中不存在

    To better handle this, you can create a function like this:

    为了更好地处理此问题,您可以创建如下函数:

    Function getWorkSheet(ByVal WorkSheetName As String) As Worksheet
        On Error GoTo EH
        Set getWorkSheet = Worksheets(WorkSheetName)
        Exit Function
    EH:
        Set getWorkSheet = Nothing
    End Function
    
    Function renameWorkSheet(ByRef ws As Worksheet, ByVal NewWsName As String) As Boolean
        On Error GoTo EH
        Debug.Print ws.Name & " to " & NewWsName
        If getWorkSheet(NewName) Is Nothing Then
            ws.Name = NewWsName
            renameWorkSheet = True
        Else
            'New Worksheet Name already exists
            renameWorkSheet = False
        End If
        Exit Function
    EH:
        renameWorkSheet = False
    End Function 
    

    Function getWorkSheet will be used to look up if there's an existing worksheet with the name that passes to that function. In this case, this function will return a worksheet object. Of course, you can customize your own codes to return as a Boolean instead, for example, but it's all up to you how you design your code.

    函数getWorkSheet将用于查找是否存在名称传递给该函数的现有工作表。 在这种情况下,此函数将返回一个工作表对象。 当然,例如,您可以自定义自己的代码以返回为布尔值,但是这完全取决于您如何设计代码。

    Function renameWorkSheet will be used to handle the rename worksheet process. First, it checks if the worksheet exists before the code proceeds to rename it.

    函数renameWorkSheet将用于处理重命名工作表过程。 首先,它在代码进行重命名之前检查工作表是否存在。

    So, we can directly implement the rename process by using code like this:

    因此,我们可以使用以下代码直接实现重命名过程:

    renameWorkSheet Worksheets("Sheet1"), "New Sheet Name"
    renameWorkSheet Worksheets("Sheet2"), "New Sheet Name (2)" 
    

    which should rename the worksheets accordingly

    应该相应地重命名工作表

    So far so good, right?

    到目前为止一切顺利,对吗?

    Now, what we can do is to rename the worksheets based on a set of rules, by knowing or without knowing how many of worksheets in total we should be renaming.

    现在,我们可以做的是基于一组规则来重命名工作表,方法是知道(或不知道)总共应该重命名多少个工作表。

    To do that, I would think we can list out the requirements we needed, such as:

    为此,我认为我们可以列出所需的要求,例如:

    1) What to be compared, such as fixed values, or cell values, etc.

    1 )要比较的内容,例如固定值或单元格值等。

    2) The comparison method, such as exact match, partial match, or match by using start with, etc.

    2 )比较方法,例如精确匹配,部分匹配或通过使用start with进行匹配等。

    To address the above, I have come out with a  data structure like this:

    为了解决上述问题,我提出了这样的数据结构:

    Private Enum CompareMethod
        Exact = 1
        Within = 2
        StartWith = 3
    End Enum
    
    Private Enum ReadFrom
        Cell = 1
        FixedValue = 2
    End Enum
    
    Private Type WorkSheetSettings
        CompareKey As String
        CompareMethod As CompareMethod
        ReadFrom As ReadFrom
        ReplaceWith As String
        ReadFrom2 As ReadFrom
        ReplaceWith2 As String
        JoinString As String
    End Type 
    

    Enum is being used to define the possible values for our own defined type.

    枚举被用来为我们自己定义的类型定义可能的值。

    Type is being used to declare a user-defined type.

    类型用于声明用户定义的类型。

    In addition, we would need a function getNewWsName to generate the new worksheet name based on the options given above:

    另外,我们将需要一个函数getNewWsName来基于上面给出的选项生成新的工作表名称:

    Function getNewWsName(ByRef ws As Worksheet, ByRef config As WorkSheetSettings) As String
        If config.ReplaceWith <> "" Then
            If config.ReadFrom = Cell Then
                getNewWsName = ws.Range(config.ReplaceWith).Value
            Else
                getNewWsName = config.ReplaceWith
            End If
        End If
        If config.ReplaceWith2 <> "" Then
            If config.ReadFrom2 = Cell Then
                getNewWsName = getNewWsName & config.JoinString & ws.Range(config.ReplaceWith2).Value
            Else
                getNewWsName = getNewWsName & config.JoinString & config.ReplaceWith2
            End If
        End If
    End Function 
    

    Now, to rename "Sheet1" to "New Sheet Name", we can implement this:

    现在,要将“ Sheet1 ”重命名为“ New Sheet Name ”,我们可以实现以下功能:

        Dim NewWsName As String
        Dim ws As Worksheet
        Dim myConfig As WorkSheetSettings
        
        myConfig.CompareKey = "Sheet1"
        myConfig.CompareMethod = Exact
        myConfig.ReadFrom = FixedValue
        myConfig.ReplaceWith = "New Sheet Name"
        
        Set ws = getWorkSheet(myConfig.CompareKey)
        If Not ws Is Nothing Then
            NewWsName = getNewWsName(ws, myConfig)
            renameWorkSheet ws, NewWsName
        End If 
    

    In the event that we would like to rename Sheet1 based on the cell value of Sheet1's cell, A1, we can implement the same by using code:

    如果我们想基于Sheet1的单元格A1的单元格值重命名Sheet1,我们可以使用以下代码来实现:

        Dim NewWsName As String
        Dim ws As Worksheet
        Dim myConfig As WorkSheetSettings
        
        myConfig.CompareKey = "Sheet1"
        myConfig.CompareMethod = Exact
        myConfig.ReadFrom = Cell
        myConfig.ReplaceWith = "A1"
       
        Set ws = getWorkSheet(myConfig.CompareKey)
        If Not ws Is Nothing Then
            NewWsName = getNewWsName(ws, myConfig)
            renameWorkSheet ws, NewWsName
        End If 
    

    To replace multiple worksheets all together, we can simply declare the WorkSheetSettings as an array with the settings, like:

    要一起替换多个工作表,我们可以简单地将WorkSheetSettings声明为具有设置的数组,例如:

        Dim NewWsName As String
        Dim ws As Worksheet
        Dim myConfig(1) As WorkSheetSettings
        
        myConfig(0).CompareKey = "Sheet1"
        myConfig(0).CompareMethod = Exact
        myConfig(0).ReadFrom = Cell
        myConfig(0).ReplaceWith = "A1"
        
        myConfig(1).CompareKey = "Sheet2"
        myConfig(1).CompareMethod = Exact
        myConfig(1).ReadFrom = FixedValue
        myConfig(1).ReplaceWith = "New Sheet Name (2)"
        
        For i = 0 To UBound(myConfig)
            Set ws = getWorkSheet(myConfig(i).CompareKey)
            If Not ws Is Nothing Then
                NewWsName = getNewWsName(ws, myConfig(i))
                renameWorkSheet ws, NewWsName
            End If
        Next 
    

    You can try exploring the possible combinations by revisiting the rules:

    您可以尝试通过重新访问规则来探索可能的组合:

    1) What to be compared, such as fixed values, or cell values, etc.

    1 )要比较的内容,例如固定值或单元格值等。

    2) The comparison method, such as exact match, partial match, or match by using start with, etc.

    2 )比较方法,例如精确匹配,部分匹配或通过使用start with进行匹配等。

    Real Scenario Implementation

    实际方案实施

    Based on the question posted recently: Needs Help With Conditional Renaming for Several Tabs

    基于最近发布的问题: 需要几个标签的条件重命名的帮助

    For the 1st tab, the "Allocation" tab, I need to rename it as “Master_CellD28Value” which means that if Cell D28’s value is A123 - FIFO, the tab should be renamed to “Master_A123 - FIFO”.

    对于第一个选项卡,即“分配”选项卡,我需要将其重命名为“ Master_CellD28Value”,这意味着如果单元格D28的值为A123-FIFO,则该选项卡应重命名为“ Master_A123-FIFO”。

    For the 6th and 7th tab, the "ESD Trf Qty" and "EVNL Trf Qty" tabs, I need it to be renamed like this: The part before “ Trf Qty”_Cell C28’s value. For example, if EVNL Trf Qty tab’s cell C28 value is A123 - LIFO then the tab should be renamed to “EVNL_A123 - LIFO”

    对于第6和第7个选项卡,“ ESD Trf数量”和“ EVNL Trf数量”选项卡,我需要像这样重命名:“ Trf数量” _Cell C28值之前的部分。 例如,如果“ EVNL Trf数量”选项卡的单元格C28值为A123-LIFO,则该选项卡应重命名为“ EVNL_A123-LIFO”



    The tabs which are named "By Ctrn-EIN", "By Ctrn-EMSB", "By Ctrn-ETH", "By Ctrn-EPC" and "By Ctry-IDC", these need to be renamed to “CellE25Value_CellC28Value”. If Cell E25 Value’s is Canada and Cell C28’s Value is B987 -123 then the tab should be renamed to “Canada_B987 - 123”

    名为“按Ctrn-EIN”,“按Ctrn-EMSB”,“按Ctrn-ETH”,“按Ctrn-EPC”和“按Ctry-IDC”的选项卡,这些选项卡需要重命名为“ CellE25Value_CellC28Value”。 如果单元格E25的值是加拿大,单元格C28的值是B987 -123,则该选项卡应重命名为“ Canada_B987-123”



    As an error proofing method, the last tab, the subset list should be left alone.

    作为防错方法,最后一个选项卡,子集列表应保留为空。

    This actually can be done by simply changing the rules and apply the code below:

    实际上,只需更改规则并应用以下代码即可完成此操作:

    Sub RenameWorkSheets()
        Dim NewWsName As String
        Dim ArrPrefix(3) As WorkSheetSettings
        
        ArrPrefix(0).CompareKey = "Allocation"
        ArrPrefix(0).CompareMethod = Exact
        ArrPrefix(0).ReadFrom = FixedValue
        ArrPrefix(0).ReplaceWith = "Master_"
        ArrPrefix(0).ReadFrom2 = Cell
        ArrPrefix(0).ReplaceWith2 = "D28"
        
        ArrPrefix(1).CompareKey = "ESD "
        ArrPrefix(1).CompareMethod = StartWith
        ArrPrefix(1).ReadFrom = FixedValue
        ArrPrefix(1).ReplaceWith = "ESD_"
        ArrPrefix(1).ReadFrom2 = Cell
        ArrPrefix(1).ReplaceWith2 = "C28"
        
        ArrPrefix(2).CompareKey = "EVNL "
        ArrPrefix(2).CompareMethod = StartWith
        ArrPrefix(2).ReadFrom = FixedValue
        ArrPrefix(2).ReplaceWith = "EVNL_"
        ArrPrefix(2).ReadFrom2 = Cell
        ArrPrefix(2).ReplaceWith2 = "C28"
        
        ArrPrefix(3).CompareKey = "By "
        ArrPrefix(3).CompareMethod = StartWith
        ArrPrefix(3).ReadFrom = Cell
        ArrPrefix(3).ReplaceWith = "E25"
        ArrPrefix(3).ReadFrom2 = Cell
        ArrPrefix(3).ReplaceWith2 = "C28"
        ArrPrefix(3).JoinString = "_"
        
        Dim ws As Worksheet
        
        For Each ws In ActiveWorkbook.Worksheets
            For i = 0 To UBound(ArrPrefix)
                Select Case ArrPrefix(i).CompareMethod
                Case CompareMethod.Exact
                    If ws.Name = ArrPrefix(i).CompareKey Then
                        NewWsName = getNewWsName(ws, ArrPrefix(i))
                        renameWorkSheet ws, NewWsName
                        Exit For
                    End If
                Case CompareMethod.StartWith
                    If Left(ws.Name, Len(ArrPrefix(i).CompareKey)) = ArrPrefix(i).CompareKey Then
                        NewWsName = getNewWsName(ws, ArrPrefix(i))
                        renameWorkSheet ws, NewWsName
                        Exit For
                    End If
                Case CompareMethod.Within
                    If InStr(ws.Name, ArrPrefix(i).CompareKey) > 0 Then
                        NewWsName = getNewWsName(ws, ArrPrefix(i))
                        renameWorkSheet ws, NewWsName
                        Exit For
                    End If
                End Select
            Next
        Next
    End Sub 
    

    As you can see, the main code remains the same. Hence, minimal code changes are needed.

    如您所见,主要代码保持不变。 因此,需要最少的代码更改。

    Note:

    注意:

    In the case you see the worksheet name is not being renamed, there could be some possibilities:

    如果您看到工作表名称未重命名,则可能存在一些可能性:

    1) You provided an invalid sheet name that needs to be renamed

    1 )您提供了一个无效的工作表名称,需要重命名

    2) You provided an empty new sheet name

    2 )您提供了一个空白的新工作表名称

    3) You provided a new sheet name that is over the limited allowed in Excel (max allowed is 31 chars)

    3 )您提供的新工作表名称超出了Excel中允许的限制(允许的最大字符数为31个字符)

    4) You provided a new sheet name that already exists

    4 )您提供了一个已经存在的新工作表名称

    Well, we are done! And here is the complete code to share with you all:

    好了,我们完成了! 以下是与大家共享的完整代码:

    Private Enum CompareMethod
        Exact = 1
        Within = 2
        StartWith = 3
    End Enum
    
    Private Enum ReadFrom
        Cell = 1
        FixedValue = 2
    End Enum
    
    Private Type WorkSheetSettings
        CompareKey As String
        CompareMethod As CompareMethod
        ReadFrom As ReadFrom
        ReplaceWith As String
        ReadFrom2 As ReadFrom
        ReplaceWith2 As String
        JoinString As String
    End Type
    
    Sub RenameWorkSheets()
        Dim NewWsName As String
        Dim ArrPrefix(3) As WorkSheetSettings
       
        ArrPrefix(0).CompareKey = "Allocation"
        ArrPrefix(0).CompareMethod = Exact
        ArrPrefix(0).ReadFrom = FixedValue
        ArrPrefix(0).ReplaceWith = "Master_"
        ArrPrefix(0).ReadFrom2 = Cell
        ArrPrefix(0).ReplaceWith2 = "D28"
       
        ArrPrefix(1).CompareKey = "ESD "
        ArrPrefix(1).CompareMethod = StartWith
        ArrPrefix(1).ReadFrom = FixedValue
        ArrPrefix(1).ReplaceWith = "ESD_"
        ArrPrefix(1).ReadFrom2 = Cell
        ArrPrefix(1).ReplaceWith2 = "C28"
       
        ArrPrefix(2).CompareKey = "EVNL "
        ArrPrefix(2).CompareMethod = StartWith
        ArrPrefix(2).ReadFrom = FixedValue
        ArrPrefix(2).ReplaceWith = "EVNL_"
        ArrPrefix(2).ReadFrom2 = Cell
        ArrPrefix(2).ReplaceWith2 = "C28"
       
        ArrPrefix(3).CompareKey = "By "
        ArrPrefix(3).CompareMethod = StartWith
        ArrPrefix(3).ReadFrom = Cell
        ArrPrefix(3).ReplaceWith = "E25"
        ArrPrefix(3).ReadFrom2 = Cell
        ArrPrefix(3).ReplaceWith2 = "C28"
        ArrPrefix(3).JoinString = "_"
       
        Dim ws As Worksheet
       
        For Each ws In ActiveWorkbook.Worksheets
            For i = 0 To UBound(ArrPrefix)
                Select Case ArrPrefix(i).CompareMethod
                Case CompareMethod.Exact
                    If ws.Name = ArrPrefix(i).CompareKey Then
                        NewWsName = getNewWsName(ws, ArrPrefix(i))
                        renameWorkSheet ws, NewWsName
                        Exit For
                    End If
                Case CompareMethod.StartWith
                    If Left(ws.Name, Len(ArrPrefix(i).CompareKey)) = ArrPrefix(i).CompareKey Then
                        NewWsName = getNewWsName(ws, ArrPrefix(i))
                        renameWorkSheet ws, NewWsName
                        Exit For
                    End If
                Case CompareMethod.Within
                    If InStr(ws.Name, ArrPrefix(i).CompareKey) > 0 Then
                        NewWsName = getNewWsName(ws, ArrPrefix(i))
                        renameWorkSheet ws, NewWsName
                        Exit For
                    End If
                End Select
            Next
        Next
    End Sub
    
    Function getNewWsName(ByRef ws As Worksheet, ByRef config As WorkSheetSettings) As String
        If config.ReplaceWith <> "" Then
            If config.ReadFrom = Cell Then
                getNewWsName = ws.Range(config.ReplaceWith).Value
            Else
                getNewWsName = config.ReplaceWith
            End If
        End If
        If config.ReplaceWith2 <> "" Then
            If config.ReadFrom2 = Cell Then
                getNewWsName = getNewWsName & config.JoinString & ws.Range(config.ReplaceWith2).Value
            Else
                getNewWsName = getNewWsName & config.JoinString & config.ReplaceWith2
            End If
        End If
    End Function
    
    Function getWorkSheet(ByVal WorkSheetName As String) As Worksheet
        On Error GoTo EH
        Set getWorkSheet = Worksheets(WorkSheetName)
        Exit Function
    EH:
        Set getWorkSheet = Nothing
    End Function
    
    Function renameWorkSheet(ByRef ws As Worksheet, ByVal NewWsName As String) As Boolean
        On Error GoTo EH
        Debug.Print ws.Name & " to " & NewWsName
        If getWorkSheet(NewName) Is Nothing Then
            ws.Name = NewWsName
            renameWorkSheet = True
        Else
            'New Worksheet Name already exists
            renameWorkSheet = False
        End If
        Exit Function
    EH:
        renameWorkSheet = False
    End Function 
    

    What's more?

    更重要的是?

    Last but not least, we can further enhance our code to include features such as:

    最后但并非最不重要的一点是,我们可以进一步增强代码以包括以下功能:

    • Put the color options to worksheet's tab

      将颜色选项放入工作表的选项卡
    • Sort the worksheets according to certain orders

      根据某些顺序对工作表进行排序
    • Split the content of a worksheet into multiple worksheets

      将工作表的内容拆分为多个工作表
    • Merge the content of worksheets into a single worksheet

      将工作表的内容合并到一个工作表中

    This I think we can discuss that in future tutorials.

    我想我们可以在以后的教程中对此进行讨论。

    I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

    希望本文对您有所帮助。 鼓励您在下面提出问题,报告任何错误或对此作出任何其他评论。

    Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts...

    注意 :如果您需要有关此主题的更多“支持”,请考虑使用Experts Exchange 的“提问”功能。 我会监督提出的问题,并很高兴与其他电子工程师一起提供以这种方式提出的问题所需的任何其他支持...

    Please do not forget to press the "Thumb's Up" button if you think this article was helpful and valuable for EE members.  It also provides me with positive feedback. Thank you!

    如果您认为本文对EE成员有用且有价值,请不要忘记按“ Thumb's Up”按钮。 它还为我提供了积极的反馈。 谢谢!

    翻译自: https://www.experts-exchange.com/articles/33507/Rename-a-list-of-worksheets-using-VBA-codes-with-conditions.html

    vba工作表重命名

    展开全文
  • 代码实现的是从一个工作簿中单独保存其中一个工作表以单元格数据命名新工作簿名称
  • 在代码中用format函数取现在的时间,VBA 的 Format 函数与工作表函数 TEXT 用法基本相同,但功能更加强大,许多格式只能用于VBA 的 Format 函数,而不能用于工作表函数 TEXT 。 Format(Now, "yyyymmddhhmm") 把...
  • Sub sheet另存为工作簿() Sheets(Array("人员档案", "明细一", "明细二", "明细三")).Copy Workbooks(Workbooks.Count).SaveAs ThisWorkbook.Path & "/" & "数据备份.xls" End Sub
    Sub sheet另存为工作簿()
        Sheets(Array("人员档案", "明细一", "明细二", "明细三")).Copy
       Workbooks(Workbooks.Count).SaveAs ThisWorkbook.Path & "/" & "数据备份.xls"
    End Sub
    
    展开全文
  • worksheets仅指的是工作表,而sheets包含工作表、图表、宏表等等,在VBA中,经常在工作表之间转换或者对不同工作表中的单元格区域进行操作。通常有下面几种方法: Sub 直接使用工作表名称法() MsgBox Worksheets("我...
  • VBA 拆分工作簿并命名

    2019-01-14 14:57:00
    Sub 拆分工作簿并命名() Dim mypath As String Dim sh As Worksheet Dim file_name As String ...mypath = ThisWorkbook.Path ... sh.Copy '这条语句会复制工作表并放在一个新建工作簿 file_name...
  • VBA工作表另存为工作簿

    千次阅读 2020-08-13 17:07:10
    Sub 宏1() Application.ScreenUpdating = False '屏幕更新关闭 ...'另存在这个路径 名字是工作表名字.xls ActiveWorkbook.Close Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
  • vba工作表重命名By default, worksheet tabs in Excel are given generic names, such as Sheet1, Sheet2, and so on. If you have a lot of worksheets in your workbook, finding specific sheets can get tricky....
  • 1、如果你有现成的一个工作簿含有多个工作表的文件,想分成多个工作簿的话, 可以通过office2007或office2013等打开execl,然后通过execl里面的开发工具, 输入以下运行代码,执行就行(拆分的工作簿在你设置的路径...
  • 工作表的添加和删除 1、工作表的添加 在工作簿中添加工作表使用 Add 方法,其格式为:expression.Add(Before, After, Count, Type)。 before:指定新建的工作表在该表格之前 after:指定新建的工作表在该表格之后 ...
  • 使用vba操作工作表,实现报表汇总

    千次阅读 2020-07-07 10:53:49
    有哪些方法和属性? 方法也可以理解为动作,的方法有select,add, delete, copy,的属性有count,name 假设工作簿中按顺序新建3张表叫1月和2月和3 月。如何切换到第2张表? Sheet2.select //sheet2是的...
  • Range("A" & x).Value Workbooks(Sname).Activate With Workbooks(Sname) .Sheets(1).name = Mid(Sname, 1, Len(Sname) - 5) '更改工作表名称 End With Sheets(1).Copy Before:=Workbooks("x.xlsx").Sheets(2) Next ...
  • 沿用上一篇关于拆分excel工作表的文章的引子,本文分享下多个工作表合并的VBA功能。案例仍使用上篇文章的例子。已知有BS、HR等多个部门,分别存放在独立的以部门命名工作表中,现需要将多个部门的工作表合并为包含...
  • 我们用excel做了一套完整的台帐系统,但上报报表时发现,我们用了大量的链接,如果单独复制到新工作表,链接中断数据会出错,这个宏提出了解决方案
  • VBA增加sheet,重名时自动命名

    千次阅读 2007-09-23 14:10:00
    VBA增加sheet,如何在出现重名时自动命名?如 Sheets.Add ActiveSheet.Name = "mysheet"如果已经存在名称为“mysheet”的sheet,如何使增加的sheet名称自动加上一个括号变成"mysheet(1)"呢? 即类似在某文件夹内...
  • VBA单元格、工作表、工作簿

    千次阅读 2019-04-30 23:08:35
    详解VBA单元格、工作表、工作簿各种表示、方法,注意事项。
  • 需求① 在一个工作簿(workbook)中根据已知的工作表名称创建多个工作表并分别命名,可以创建同时当然需要研究增删改工作表。思路:研究vba的基础语法,知道了workbooks(工作簿集)、workbook(工作簿)、worksheet...
  • 一键删除特定名称的工作表
  • VBA 工作表 worksheet常用事件

    千次阅读 2020-01-31 10:36:01
    1 工作表 sheet的添加 worksheets.add 或 sheets.add 1.1 基本语法 Sheets.Add 方法 新建工作表、图表或宏表。新建的工作表将成为活动工作表。 语法 表达式.Add(Before, After, Count, Type) 表达式 一个代表 ...
  • 学习资源:《Excel VBA从入门到进阶》第10集 by蓝色幻想一、Excel工作表的分类Excel工作表有两大类,一类是我们平常用的工作表(worksheet),另一类是图表、宏表等。这两类的统称是sheets。在工作表的标签页上右击—...
  • 工作表1导出来成为一个单独的工作簿,命名1.xlsx 将工作表2导出来成为一个单独的工作簿,命名2.xlsx 将工作表3导出来成为一个单独的工作簿,命名3.xlsx 将工作表4导出来成为一个单独的工作簿,命名4.xlsx 将工作表5导...
  • 复制指定目录下excel工作簿中同名工作表,该代码将在相同目录下创建汇总工作簿,各工作簿中同名工作表将被分别复制到汇总工作簿的不同表中(汇总工作簿中各工作表以分工作簿名命名)。 所有要汇总的工作簿在同一个...
  • Sub 新建工作表() Dim ShtName As String, sht As Worksheet '声明变量 ShtName = Format(Date, “yyyy-mm-dd”) '将今日日期格式化为“yyyy-mm-dd”格式 On Error Resume Next '如果执行时出错,那么继续执行下一句...
  • VBA在Excel中的应用用VBA将同一工作簿中不同工作表拆成独立文件问题描述1.在“开发工具”选项卡中选择”Visual Basic”2.插入模块运行结果可能存在的问题 用VBA将同一工作簿中不同工作表拆成独立文件 问题描述 有一...
  • Public Sub 更改名称() For i = 2 To Worksheets.Count Worksheets(i).Name = Worksheets("目录").Cells(i, 2).Value Next End Sub
  • EXCEL按照空行分割工作表并保存为独立工作簿后按照指定表名重命名代码: 参考原代码: Sub 插入() On Error GoTo errHandler Dim i As Long '按照标题插入空行 For i = 2 To Range("A1").CurrentRegion.Count - 1 ...
  • Sub BJ_Union删除大区_汇总款式销售信息排序() Application.DisplayAlerts = False Dim St1 As Object, St2 As Object, Cel As Object, Rng As Object Dim Row1 As Integer '每次运行程序之前,删掉之前生成的排名...
  • 业务场景: 日常工作中,我们可能会遇到需要批量导出excel表中的图片的情况,按照网友的做法,批量导出excel中的图片主要有几种...例如:有如下一个工作表,需要将其中的图片批量导出 解决方案: 利用VBA遍历工作

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,765
精华内容 1,106
关键字:

vba增加工作表并命名