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

Sub RenameSheet()

Dim rs As Worksheet

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

End Sub

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

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

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

pip install pypiwin32

# 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()