|
楼主 |
发表于 2014-12-22 23:49
|
显示全部楼层
已经有大量帮助解决问题,发上来供大家参考:
Sub Macro1()
Dim Fso As Object, Folder As Object
Dim i&, n&, a, b, wb As Workbook, wb2 As Workbook, p$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
a = Array(4, 5, 3, 2)
b = Array("基本情况(填表)", "老干部情况", "医务人员", "医疗设备")
For i = 1 To 4
Sheets(i).[a1].CurrentRegion.Offset(a(i - 1)).Clear
Next
Set Fso = CreateObject("Scripting.FileSystemObject")
p = ThisWorkbook.Path & "\各省汇总"
With ThisWorkbook
For Each SubFolder In Fso.GetFolder(p).SubFolders
n = 0
For Each File In SubFolder.Files
n = n + 1
Set wb = Workbooks.Open(File)
For i = 0 To 3
wb.Sheets(b(i)).[a1].CurrentRegion.Offset(a(i)).Copy .Sheets(b(i)).[a65536].End(xlUp).Offset(1)
Next
If n = 1 Then
wb.Sheets(b).Copy
Set wb2 = ActiveWorkbook
Else
For i = 0 To 3
wb.Sheets(b(i)).[a1].CurrentRegion.Offset(a(i)).Copy wb2.Sheets(b(i)).[a65536].End(xlUp).Offset(1)
Next
End If
wb.Close False
Next
wb2.Close True, p & "\" & SubFolder.Name & "汇总表.xls"
Next
End With
Set Fso = Nothing
Application.ScreenUpdating = True
MsgBox "ok"
End Sub
|
|