|
- Sub test()
- Dim rng As Range, rngs As Range
- Application.ScreenUpdating = False
- Sheets("Sheet1").Range("a3:l65536").ClearContents
- With Sheet4
- a = .Cells(Rows.Count, 4).End(3).Row
- .Range(.Cells(4, 4), .Cells(a, 8)).Copy Sheets("Sheet1").[a3]
- .Range("K4:k" & a).Copy Sheets("Sheet1").[k3]
- End With
- With Sheet3
- i = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(3).Row + 1
- b = .Cells(Rows.Count, 4).End(3).Row
- .Range(.Cells(4, 4), .Cells(b, 8)).Copy Sheets("Sheet1").Cells(i, 1)
- .Range("J4:j" & b).Copy Sheets("Sheet1").Cells(i, "F")
- .Range("J4:j" & b).Copy Sheets("Sheet1").Cells(i, "g")
- .Range("J4:j" & b).Copy Sheets("Sheet1").Cells(i, "h")
- .Range("J4:j" & b).Copy Sheets("Sheet1").Cells(i, "j")
- .Range("J4:j" & b).Copy Sheets("Sheet1").Cells(i, "k")
- End With
- With Sheet5
- i = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(3).Row + 1
- c = .Cells(Rows.Count, 4).End(3).Row
- .Range(.Cells(4, 4), .Cells(c, 8)).Copy Sheets("Sheet1").Cells(i, 1)
- .Range("J4:j" & c).Copy Sheets("Sheet1").Cells(i, "F")
- .Range("J4:j" & c).Copy Sheets("Sheet1").Cells(i, "g")
- .Range("J4:j" & c).Copy Sheets("Sheet1").Cells(i, "h")
- .Range("J4:j" & c).Copy Sheets("Sheet1").Cells(i, "j")
- .Range("J4:j" & c).Copy Sheets("Sheet1").Cells(i, "k")
- .Range("J4:j" & c).Copy Sheets("Sheet1").Cells(i, "l")
- End With
- With Sheet8
- i = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(3).Row + 1
- d = .Cells(Rows.Count, 4).End(3).Row - 3
- .Range(.Cells(5, 3), .Cells(d, 7)).Copy Sheets("Sheet1").Cells(i, 1)
- .Range("o5:o" & d).Copy Sheets("Sheet1").Cells(i, "g")
- .Range("o5:o" & d).Copy Sheets("Sheet1").Cells(i, "h")
- .Range("v5:v" & d).FormulaR1C1 = "=RC[-7]-0.2"
- .Range("v5:v" & d).Copy
- Sheets("Sheet1").Cells(i, "f").PasteSpecial Paste:=xlPasteValues
- Sheets("Sheet1").Cells(i, "j").PasteSpecial Paste:=xlPasteValues
- End With
- With Sheets("Sheet1").UsedRange
- .Interior.Pattern = xlNone
- End With
- i = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(3).Row
- Range("x3:x" & i) = "=COUNTIF(RC[-18]:RC[-12],"""")"
- For m = i To 3 Step -1
- If Cells(m, "X").Value = 7 Then
- Cells(m, "X").EntireRow.Delete
- End If
- Next
- For Each rng In Range("a3:a" & i)
- If rng.Value = "品名" Then rng.EntireRow.Delete
- Next
- [x1:x65536].ClearContents
- Range("A3:A65535").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
- Columns("A:M").RowHeight = 12.5
- Sheets("Sheet1").Range("a3:l" & i).Borders.LineStyle = xlContinuous
- Application.ScreenUpdating = True
- End Sub
复制代码 空行删除已解决 物料单号为空 不知道是什么原因 数据是直接复制的 没有改动 可能是原来数据问题 |
|