|
发表于 2017-6-3 08:29
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Dim WB As Workbook
- Dim F, E
- Dim arr
- F = False
- For Each E In Workbooks
- If E.Name = "2017年新订单进度表.xls" Then
- F = True
- If MsgBox("请关闭当前操作界面中已经打开的【2017年新订单进度表】后再操作!", 32 + 256) = 6 Then
- Exit Sub
- End If
- End If
- Next
- If F = False Then
- Set fso = CreateObject("scripting.filesystemobject")
- Set fld = CreateObject("Shell.Application").BrowseForFolder(0, "请选择文件夹", 0, 0)
- If fld Is Nothing Then End
- fp = fld.Self.Path
- Workbooks.Open Filename:=fp & "" & "2017年新订单进度表.xls" '读入
- arr = Sheets("在制(未完成)").UsedRange
- ActiveWorkbook.Close savechanges:=False
- lastrow = Cells(Rows.Count, 6).End(3).Row '当前工作表
- On Error Resume Next
- For n = 4 To lastrow
- For i = 2 To UBound(arr)
- If ActiveSheet.Cells(n, 6) = arr(i, 5) Then '前面是当前工作表,后面是数据源工作薄
- ActiveSheet.Cells(n, 14) = arr(i, 1)
-
- End If
- Next
- Next
- End If
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|