|
Sub wkqd() '发布表和有效表同时打开执行代码
Dim k As Long, m As Integer, str As String, adr, strBH As Byte
On Error Resume Next
Workbooks("发布表.xls").Activate
k = Sheets("分发总清单").Cells(Rows.Count, 1).End(3).Row + 1
Sheets("每次分发").Activate
For m = Cells(Rows.Count, 1).End(3).Row To 2 Step -1
str = Cells(m, 2).Text
strBH = Len(Cells(m, 6))
If strBH = 5 Then
If InStr(Cells(m, 2), "JL") > 0 Then
Workbooks("有效表.xls").Activate
Sheets("记录清单").Activate
Columns("b:b").Find(what:=str).Select
adr = Selection.Row
Workbooks("发布表.xls").Sheets("每次分发").Rows(m).Copy Range("a" & adr)
Else
Workbooks("有效表.xls").Activate
Sheets("文件清单").Activate
Columns("b:b").Find(what:=str).Select
adr = Selection.Row
Workbooks("发布表.xls").Sheets("每次分发").Rows(m).Copy Range("a" & adr)
End If
Else
If InStr(Cells(m, 2), "JL") > 0 Then
Workbooks("有效表.xls").Activate
Sheets("作废记录").Activate
adr = Range("b65536").End(3).Row + 1
Workbooks("发布表.xls").Sheets("每次分发").Rows(m).Copy Range("a" & adr)
Workbooks("发布表.xls").Sheets("每次分发").Rows(m).Delete
Else
Workbooks("有效表.xls").Activate
Sheets("作废文件").Activate
adr = Range("b65536").End(3).Row + 1
Workbooks("发布表.xls").Sheets("每次分发").Rows(m).Copy Range("a" & adr)
Workbooks("发布表.xls").Sheets("每次分发").Rows(m).Delete
End If
End If
Workbooks("发布表.xls").Activate
Sheets("每次分发").Activate
Next m
Range("a2:j" & Cells(Rows.Count, 1).End(3).Row).Select
Selection.Copy Sheets("分发总清单").Range("a" & k)
End Sub
|
|