背景:将文件夹中给定excel文件的目标单元格内容,复制到指定excel文件中,并且对其中第二列单元格(单位名称)进行规范命名。 编码目的:省去每一次复制、粘贴数据时间,提高学习强国平台管理效率。
Option Explicit
Sub copy()
Dim temp_Workbook, Sum_Workbook, File_sum, File_Dir, myfile
'定义待复制单元格行列号
Dim sum_row, sum_column, i, j As Long
sum_row = 4
sum_column = 2
'打开汇总表格
File_sum = "D:\2022年\8月\学习强国\9月\学习强国2022年 月 日学习情况.xls"
Set Sum_Workbook = GetObject(File_sum)
myfile = ""
'搜寻工作薄所在路径下的给定xls文件
File_Dir = ThisWorkbook.Path
myfile = Dir(File_Dir & "\*数据情况*.xlsx*")
Do While myfile <> "" '若不为空,遍历开始
If myfile Like "*数据情况*" Then
'打开工作薄、表格-----
Set temp_Workbook = GetObject(File_Dir & "\" & myfile)
Debug.Print "------" & myfile & "复制处理开始:"
'复制单元格数据
For i = 4 To temp_Workbook.Worksheets("Sheet1").UsedRange.Rows.Count
For j = 2 To temp_Workbook.Worksheets("Sheet1").UsedRange.Columns.Count
Sum_Workbook.Worksheets("全区").Cells(sum_row, sum_column) = temp_Workbook.Worksheets("Sheet1").Cells(i, j)
Debug.Print temp_Workbook.Worksheets("Sheet1").Cells(i, j)
sum_column = sum_column + 1
Next
sum_column = 2
sum_row = sum_row + 1
Next
End If
myfile = Dir() '读入下一个文件
Loop
Debug.Print "复制完成"
For i = 4 To Sum_Workbook.Worksheets("全区").UsedRange.Rows.Count
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*医疗*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区医保局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*工商*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区工商联"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*融媒*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区融媒体中心"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*宣传部*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委宣传部"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*检察院*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区检察院"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*执法*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区综合执法局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*应急*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区应急局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*文化*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区文广旅游局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*公安*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "市公安局船山区分局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*经科*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区经科局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*人大*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区人大"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*纪委监委*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区纪委监委"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*军人*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区退役军人局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*国有*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区国资局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*妇女*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区妇联"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*生态*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "船山生态环境局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*组织*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委组织部"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*司法*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区司法局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*党校*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委党校"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*总工*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区总工会"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*卫生*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区卫生健康局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*乡村*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区乡村振兴局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*审计*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区审计局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*团区委*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "团区委"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*残联*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区残联"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*统计*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区统计局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*政法委*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委政法委"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*市场*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区市场监管局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*统战*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委统战部"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*农民*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区农民工服务中心"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*科协*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区科协"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*政府办*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区政府办"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*税务*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区税务局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*观音湖*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "观音湖文旅园区"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*编办*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委编办"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*教育*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区教育和体育局"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*法院*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区法院"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*高新区*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "船山园区"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*龙凤*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "龙凤镇"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*住建*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区住建局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*河沙*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "河沙镇"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*永兴*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "永兴镇"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*自然*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区自然资源和规划局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*人社*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区人力资源社会保障局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*老池*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "老池镇"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*行政*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区行政审批局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*财政*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区财政局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*育才*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "育才路街道"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*仁里*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "仁里镇"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*镇江*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "镇江寺街道"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*永河园*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "现代农业产业园"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*民政*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区民政局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*水利*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区水利局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*交通*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区交通运输局"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*档案*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区档案馆"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*农业*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区农业农村局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*唐家*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "唐家乡"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*委办*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区委办"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*介福*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "介福路街道"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*凯旋*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "凯旋路街道"
End If
'------------------------------------------------------------------------
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*发改*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区发改局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*桂花镇*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "桂花镇"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*政协*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区政协"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*信访*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "区信访局"
End If
If Sum_Workbook.Worksheets("全区").Cells(i, 2) Like "*南津路*" Then
Sum_Workbook.Worksheets("全区").Cells(i, 2) = "南津路街道"
End If
Next
End Sub