Sub 小晕()
'请先建立一个新的表(带宏的),工作簿名字叫 汇总
'添加工作表从Sheet1到Sheet10
'然后在这个目录D:\新建文件夹\1.xlsx 建立新表(文件拓展名请注意,要一样的)
'一共建立2个就 分别是 1.xlsx 2.xlsx ... 10.xlsx就可以。
'第一个表
Workbooks.Open ("D:\新建文件夹\1.xlsx")
Windows("1.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("1.xlsx").Activate
ActiveWindow.Close
'第二个表
Workbooks.Open ("D:\新建文件夹\2.xlsx")
Windows("2.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("2.xlsx").Activate
ActiveWindow.Close
'第三个表
Workbooks.Open ("D:\新建文件夹\3.xlsx")
Windows("3.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet3").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("3.xlsx").Activate
ActiveWindow.Close
'第四个表
Workbooks.Open ("D:\新建文件夹\4.xlsx")
Windows("4.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet4").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("4.xlsx").Activate
ActiveWindow.Close
'第五个表
Workbooks.Open ("D:\新建文件夹\5.xlsx")
Windows("5.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet5").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("5.xlsx").Activate
ActiveWindow.Close
'第六个表
Workbooks.Open ("D:\新建文件夹\6.xlsx")
Windows("6.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet6").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("6.xlsx").Activate
ActiveWindow.Close
'第七个表
Workbooks.Open ("D:\新建文件夹\7.xlsx")
Windows("7.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet7").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("7.xlsx").Activate
ActiveWindow.Close
'第八个表
Workbooks.Open ("D:\新建文件夹\8.xlsx")
Windows("8.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet8").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("8.xlsx").Activate
ActiveWindow.Close
'第九个表
Workbooks.Open ("D:\新建文件夹\9.xlsx")
Windows("9.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet9").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("9.xlsx").Activate
ActiveWindow.Close
'第十个表
Workbooks.Open ("D:\新建文件夹\10.xlsx")
Windows("10.xlsx").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("汇总.xlsm").Activate
Sheets("Sheet10").Select
Cells.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("10.xlsx").Activate
ActiveWindow.Close
'文件名变量还在看,这个方法但也能解决你的问题,累赘了点
End Sub
有问题追问
温馨提示:答案为网友推荐,仅供参考