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

暗香疏影 创作者
  2020-12-02 2020-12-02 创建   2020-12-02 2020-12-02 更新      

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

Microsoft Docs

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

Step 2: Insert –>Module

Step 3:

将以下代码复制进去

1
2
3
4
5
6
7
8
9
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

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

1
2
3
4
5
6
7
8
9
10
11
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批量修改特定位置颜色

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

1
2
3
4
5
6
7
8
9
10
11
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

有条件修改目前工作表

1
2
3
4
5
6
7
8
9
10
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

有条件修改全部工作表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
修改全部工作表的代码为
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: 复制以下代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
pip install pypiwin32

Step 2: 使用pycharm并填写代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# 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某列数据为依据分成一个新的工作表

  • 标题: Excel中如何批量重命名工作表与将每个工作表导出到单独Excel文件
  • 作者: 暗香疏影
  • 创建于 : 2020-12-02 00:00:00
  • 更新于 : 2020-12-02 00:00:00
  • 链接: https://blog.23ikr.com/2020/12/02/2020-12-02-Excel-Worksheet-rename-and-separate/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
评论
目录
Excel中如何批量重命名工作表与将每个工作表导出到单独Excel文件
  1. Excel中通过VBA批量重命名工作表Worksheet
  2. Excel中通过VBA批量修改特定位置颜色
    1. 单个无条件修改全部工作表
    2. 有条件修改目前工作表
    3. 有条件修改全部工作表
  3. 根据Excel特定列分成不同工作表 - Excel VBA
  4. 将每个工作表导出到单独Excel文件 - Excel VBA
  5. 将每个工作表导出到单独Excel文件 - Python
  6. 将Excel特定列直接分成单独文件 - Python