如何用VBA在一个新的工作簿工作表中汇总某文件夹下所有工作簿下某工作表某单元格数据?

Answer 1

'打开文件在菜栏有一个"表格汇总"按扭
Sub auto_open()
MenuBars(xlWorksheet).Reset
Set mycommandbar = CommandBars("standard")
Set mybutton = mycommandbar.Controls.Add(Type:=msoControlButton)
With mybutton
.Style = msoButtonCaption
.Caption = "表格汇总"
.Enabled = True
.OnAction = "ABCD"
End With
End Sub
Sub auto_close()
Set mycommandbar = CommandBars("standard")
For Each mybutton In mycommandbar.Controls
If mybutton.Caption = "表格汇总" Then mybutton.Delete
Next
End Sub
Sub ABCD()
Dim lj As String
Dim dirname As String
Dim nm As String
Sheets(1).Select
lj = ActiveWorkbook.Path
nm = ActiveWorkbook.Name
dirname = Dir(lj & "\*.*")
Range("A2:AD200").Select
Selection.Delete Shift:=xlToLeft
Do While dirname <> ""
If dirname <> nm Then
Workbooks.Open Filename:=lj & "\" & dirname
Workbooks(nm).Activate
Workbooks(dirname).Sheets(1).Range("Q5:AD5").Copy _
Sheets(1).Range("a65536").End(xlUp).Offset(2, 0)'自己的表格修改
Workbooks(dirname).Close False
End If
dirname = Dir
Loop
Cells(20, 1).Select
End Sub

Answer 2

汇总目录下所有工作薄的指定工作表的某个单元格的回答链接
http://zhidao.baidu.com/question/429391132.html?oldq=1
汇总目录下所有工作薄的指定工作表到一个工作表的链接
http://zhidao.baidu.com/question/391751457.html?oldq=1
复制目录下所有工作薄的指定工作表到一个工作薄的链接
http://zhidao.baidu.com/question/449452072.html?oldq=1

Answer 3

可以做，“将工作簿工作表格式设置好，再将这些工作簿发给若干单位填报”，要知道格式和模拟数据，才能有奋斗目标，才能进行调试，才能确信所做的工作是正确的。