|
楼主 |
发表于 2019-6-12 10:12
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 纯黑奥利奥 于 2019-6-12 10:14 编辑
大神们,这是我照着修改的,不知道哪里出问题了,帮忙看下吧。
Sub Copy_Source01()
Application.ScreenUpdating = False
Dim stMedd As String, tFilename As String, myName As String
Dim MaxR As Long
Dim arr
Dim sss As Single
Dim sht As Worksheet
sss = Timer
Select Case MsgBox("【是】:全部清空重新添加数据" & Chr(10) & _
"【否】:表示原有基础新增记录" & Chr(10) & _
"【取消】:表示退出操作", vbExclamation + vbYesNoCancel + vbDefaultButton3, "提示")
Case vbYes
Range("A2:E65536").ClearContents
Case vbNo
Case Else
Exit Sub
End Select
stMedd = "请选择文件目录:"
Set obMapp = CreateObject("Shell.Application").BrowseForFolder(0, stMedd, &H1)
If Not obMapp Is Nothing Then
Directory = obMapp.self.Path & "\"
Else
Exit Sub
End If
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.getfolder(Directory)
With ThisWorkbook.Sheets("汇总")
For Each f In wb.workbooksheets
If f.Name <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(f, 0, 1)
n = n + 1
MaxR = wb.Sheets("sheet1").Range("a65536").End(3).Row
wb.Sheets("sheet1").Range(B6).Copy
ThisWorkbook.Sheets("汇总").Range("A" & .[B65536].End(3).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
wb.Sheets("sheet1").Range("a5:Q" & MaxR).Copy
ThisWorkbook.Sheets("汇总").Range("B" & .[B65536].End(3).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
End If
wb.Close False
Next f
End With
arr = Array("代码", "物料代码", "物料名称", "规格型号", "单位", "数量")
ThisWorkbook.Sheets("汇总").[A1].Resize(1, 6) = arr
MsgBox "数据【复制】完毕:耗时" & Format(Timer - sss, "0.000") & " 秒!!" & Chr(10) & "共计" & n & "个文件", 64, "提示信息"
Application.ScreenUpdating = True
End Sub
|
|