|
Sub 标签()
Application.ScreenUpdating = False
Dim ar As Variant
With Sheets("资产明细")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r < 2 Then MsgBox "资产明细为空!": End
ar = .Range("a1:g" & r)
End With
gs = r - 1
With Sheets("标签模板")
rs = .Cells(Rows.Count, 1).End(xlUp).Row
If rs > 6 Then .Rows("7:" & rs).Delete
Range("B2,D2,B3:D3,B4,D4,B5,D5") = Empty
m = 7
For i = 2 To gs
.Rows("1:6").Copy .Cells(m, 1)
m = m + 6
Next i
m = 2
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
.Cells(m, 2) = ar(i, 1)
.Cells(m, 4) = ar(i, 2)
.Cells(m + 1, 2) = ar(i, 3)
.Cells(m + 2, 2) = ar(i, 5)
.Cells(m + 2, 4) = ar(i, 4)
.Cells(m + 3, 2) = ar(i, 6)
.Cells(m + 3, 4) = ar(i, 7)
m = m + 6
End If
Next i
.Select
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|