win32com：excel筛选 复制

import os.path
from win32com.client import Dispatch

excel = Dispatch("ket.Application")  # 启动wps excel 应用。Microsoft excel用Excel.Application
excel.Visible = True
end_file_basepath = r'C:\Users\meiya\Desktop\数据分省\地区'
source_basepath = r'C:\Users\meiya\Desktop\数据分省'
source_file_name = '构代码信息核对明细表.xls'
source_file = os.path.join(source_basepath,source_file_name)
wb = excel.Workbooks.Open(source_file)  # 打开excel文档

for sheet in wb.Worksheets:
    rows_max = sheet.usedRange.Rows.Count  # 获取已使用范围的最大行数
    rowA_Range = sheet.Range(f'A5:A{rows_max}').value
    rowA_set = set(rowA_Range)
    rowA = [r[0] for r in rowA_set ]

    for r in rowA:
        sheet.UsedRange.AutoFilter(Field=1, Criteria1=r, Operator=1)
        # 也可以写 sheet.Range('A1').AutoFilter(Field=1, Criteria1=r, Operator=1) 筛选时，range可以写a1，也可以写更大的范围

        for item in os.listdir(end_file_basepath):
            provience = item.split('.')[1]
            if r.find(provience) >= 0:
                tem_file = os.path.join(source_basepath,sheet.name)
                wb_tem = excel.Workbooks.Open(tem_file)  # 打开excel文档
                sheet_tem = wb_tem.Worksheets[0]
                row_mx = sheet_tem.UsedRange.Rows.Count+1
                if sheet.name == 'AA列开头为99（法人行）':
                    start = 4
                else:
                    start = 2
                sheet.Range(f'A{start}:V{sheet.UsedRange.Rows.Count}').SpecialCells(12).Copy(sheet_tem.Range(f'A{row_mx}'))
                sheet_tem.name = sheet.name

                provience_folder = os.path.join(end_file_basepath, item)
                provience_file = os.path.join(provience_folder,provience+'-'+sheet.name)
                wb_tem.SaveAs(provience_file)

wb.Save()
wb.Close()
excel.Quit()