Excel中如何批量重命名工作表与将每个工作表导出到单独Excel文件

Excel中通过VBA批量重命名工作表Worksheet

Microsoft Docs

Step 1: 打开Developer Tab找到VBA (快捷键 Alt+F11)

Step 2: Insert -->Module

Step 3:

将以下代码复制进去

Sub RenameSheet()

Dim rs As Worksheet

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

End Sub

Step 4: 按F5运行，或关闭VBA后，通过 Excel View -->Macros -->View Macros-->Run

如果指定单元格没有数据怎么办？
我们可以添加一个条件进去即可。

Sub RenameSheet()

Dim rs As Worksheet

For Each rs In Sheets
    If rs.Range("F3").Value <> "" Then
        rs.Name = rs.Range("F3")
    End If
Next rs

End Sub

Excel中通过VBA批量修改特定位置颜色

单个无条件修改全部工作表

Sub BackGroudColor()

Dim rs2 As Worksheet

For Each rs2 In Sheets
rs2.Range("C6").Interior.Color = RGB(180, 198, 231)
rs2.Range("B7").Interior.Color = RGB(255, 230, 153)
rs2.Range("E6").Interior.Color = RGB(198, 224, 180)
Next rs2

End Sub

有条件修改目前工作表

Sub Fill_Cell_Condition()

Dim rngCell As Range

For Each rngCell In Range("A6:A19")
    If Len(rngCell.Value) <> "0" Then
        rngCell.Cells.Interior.Color = RGB(255, 230, 153)
    'If Everything in A6-A19 The length of the cell value is not zero, change backgroud color. Otherwise, do nothing
 End If
Next rngCell

有条件修改全部工作表

修改全部工作表的代码为
Dim ws As Worksheet
For Each ws In Sheets
###在中间插入你想要全部工作表都修改的代码
Next ws
========================================
Sub Fill_Cell_Condition()

Dim wsFill As Worksheet
Dim i

For Each wsFill In Sheets 
    For i = 8 To 20
        If wsFill.Cells(i, 1).Value <> "" Then
        '当A8-A20里不是没有值，则着色
            wsFill.Cells(i, 1).Interior.Color = RGB(155, 30, 153)
        End If
    Next

Next wsFill

End Sub

根据Excel特定列分成不同工作表 - Excel VBA

这个在之后的文章有提及，大家可以参考一下。

如何根据Excel某列数据为依据分成一个新的工作表

将每个工作表导出到单独Excel文件 - Excel VBA

来源：KuTools Office

Step 1: Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window

Step 2: Click Insert > Module, and paste the following code in the Module Window

Step 3: 复制以下代码

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

将每个工作表导出到单独Excel文件 - Python

Step 1: 在Termianl选择pip install组件pypiwin32

pip install pypiwin32

Step 2: 使用pycharm并填写代码

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
def create_wb_from_ws():
    try:
        filepath = 'D:\sp\test.xlsx'

        from win32com.client import DispatchEx
        excel = DispatchEx("Excel.Application")

        if excel == None:
            print('-' * 100)
            print('Error: Excel is not found on this machine. Existing!')
            print('-' * 100)
            return
        else:
            print('-' * 100)
            print('Message: Excel version {0} is available.'.format(excel.version))
            print('-' * 100)

        if int(float(excel.version)) < 12:
            fileext = '.xls'
        else:
            fileext = '.xlsx'

        import os
        if not os.path.exists(filepath):
            print('The entered file path does not exists. Existing!')
            return

        filedir = os.path.join(os.path.dirname(filepath), os.path.splitext(os.path.basename(filepath))[0])
        if not os.path.exists(filedir):
            os.mkdir(filedir)

        excel.Visible = False
        excel.DisplayAlerts = False
        wb = excel.Workbooks.Open(Filename=filepath)
        wb.Application.Visible = False

        for sheet in wb.Worksheets:
            filename = os.path.join(filedir, sheet.name + fileext)
            wbnew = excel.Workbooks.Add()
            wbnew.Application.Visible = False
            sheet.Copy(Before=wbnew.Worksheets(1))

            for s in wbnew.Worksheets:
                if s.name != sheet.name:
                    wbnew.Worksheets(s.name).Delete()

            wbnew.SaveAs(filename)
            print('Saved sheet name "{0}" as a new excel file at {1}'.format(sheet.name, filename))
            wbnew.Close(SaveChanges=1)

        wb.Close(True)
        excel.Quit()
    except:
        print('-' * 100)
        print('Error occurred')
        print('-' * 100)
        raise


if __name__ == "__main__":
    create_wb_from_ws()

将Excel特定列直接分成单独文件 - Python

这个在之后的文章有提及，大家可以参考一下。

如何根据Excel某列数据为依据分成一个新的工作表