Excel中通过VBA批量重命名工作表Worksheet
Microsoft Docs
Step 1: 打开Developer Tab找到VBA (快捷键 Alt F11)
Step 2: Insert –>Module
Step 3:
将以下代码复制进去
代码语言：javascript复制Sub RenameSheet()
Dim rs As Worksheet
For Each rs In Sheets
rs.Name = rs.Range("B5")
Next rs
End SubStep 4: 按F5运行，或关闭VBA后，通过 Excel View –>Macros –>View Macros–>Run
如果指定单元格没有数据怎么办？ 我们可以添加一个条件进去即可。
代码语言：javascript复制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 SubExcel中通过VBA批量修改特定位置颜色
单个无条件修改全部工作表
代码语言：javascript复制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有条件修改目前工作表
代码语言：javascript复制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有条件修改全部工作表
代码语言：javascript复制修改全部工作表的代码为
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
这个在之后的文章有提及，大家可以参考一下。
</2021/01/18/2021-01-18-Split-excel-data-into-sheets-by-column-values/>
将每个工作表导出到单独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: 复制以下代码
代码语言：javascript复制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
代码语言：javascript复制pip install pypiwin32Step 2: 使用pycharm并填写代码
代码语言：javascript复制# 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:sptest.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
这个在之后的文章有提及，大家可以参考一下。
</2021/01/18/2021-01-18-Split-excel-data-into-sheets-by-column-values/>
