分多个步骤进行数据的整理:
A: 获取汇总数据到一个新的工作表
Sub GetExcleWS()
Dim path As String, filename As String
Dim w As Workbook, ws As Workbook
path = "C:\Users\fish.lol\Desktop\Data Collect"
filename = Dir(path & "\Summ_*.xlsx")
'ws工作簿保存所有单位excel表格花名册
'关闭提示
Application.DisplayAlerts = False
Set ws = Workbooks.Add
Do While filename <> ""
'w代表指定文件夹下每个找到的excel文件
Set w = Workbooks.Open(path & "\" & filename)
'选择工作表(此处假设sheet1),复制,并粘贴为汇总表的最后一张
w.Sheets("Summary").Copy After:=ws.Sheets(ws.Sheets.Count)
'重命名刚贴的表名为excel文件名
'ws.Worksheets(ws.Sheets.Count).Name = Mid(filename, 1, Len(filename) - 5)
ws.Worksheets(ws.Sheets.Count).Name = Range("c2")
'关闭工作簿
w.Close
'下一个