|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
请教大虾,同一个文件夹内多个工程簿,我需要在汇总工作簿内运行宏,拷贝其它工作簿的数据到汇总工作簿,但在拷贝前需要历遍该工作簿内的每个工作表,这里需要跨工作簿调用宏命令,请问application.run的后续代码如何写?
我是这样写代码的,请大虾给予指正修改,谢谢!因为文件内工作簿太多,无法打包上传,请大虾见谅
Sub 数据汇总()
Dim mypath$, myfile$, wb As Workbook, sh As Worksheet, k%
Application.DisplayAlerts = False
Range("A3:N20").ClearContents
mypath = ThisWorkbook.Path & "\"
myfile = Dir(mypath & "*.xlsm")
k = 0
Do While myfile <> ""
If myfile <> ThisWorkbook.Name Then
Workbooks.Open Filename:=mypath & myfile '打开工作簿
Set wb = GetObject(mypath & myfile)
wb.Activate
For Each sh In Worksheets '在打开的工作簿中历遍每个工作表
sh.Activate
Application.Run "'sh.name!'桩基数据计算"
k = k + 1
ThisWorkbook.Sheets("汇总表").Range("A" & (k + 2)) = k
ThisWorkbook.Sheets("汇总表").Range("B" & (k + 2)) = sh.Name
sh.Range("R3:AC3").Copy ThisWorkbook.Sheets("汇总表").Range("C" & (k + 2) & ":N" & (k + 2))
Next
wb.Close savechanges:=True
End If
Set wb = Nothing
myfile = Dir
Loop
Application.DisplayAlerts = True
End Sub
|
|