Sub 打印标签()
With Sheets("数据表")
r = .Cells(Rows.Count, 2).End(xlUp).Row
ar = .Range("b1:g" & r)
End With
m = UBound(ar) - 1
If m / 3 = Int(m / 3) Then
sl = m / 3
Else
sl = Int(m / 3)
End If
With Sheets("标签页")
rs = .Cells(Rows.Count, 1).End(xlUp).Row + 20
.Range("B1:D3,B4,D4,G1:I3,G4,I4,L1:N3,L4,N4") = Empty
.Rows("6:" & r).Delete
m = 6
For i = 1 To sl
.Rows("1:5").Copy .Cells(m, 1)
m = m + 5
Next i
m = 1
For i = 2 To UBound(ar) Step 3
If Trim(ar(i, 1)) <> "" Then
.Cells(m, 2) = ar(i, 1)
.Cells(m + 1, 2) = ar(i, 2)
.Cells(m + 2, 2) = ar(i, 3)
.Cells(m + 3, 2) = ar(i, 4)
.Cells(m + 3, 4) = ar(i, 5)
If i + 1 > UBound(ar) Then GoTo 10
.Cells(m, 7) = ar(i + 1, 1)
.Cells(m + 1, 7) = ar(i + 1, 2)
.Cells(m + 2, 7) = ar(i + 1, 3)
.Cells(m + 3, 7) = ar(i + 1, 4)
.Cells(m + 3, 9) = ar(i + 1, 5)
If i + 2 > UBound(ar) Then GoTo 10
.Cells(m, 12) = ar(i + 2, 1)
.Cells(m + 1, 12) = ar(i + 2, 2)
.Cells(m + 2, 12) = ar(i + 2, 3)
.Cells(m + 3, 12) = ar(i + 2, 4)
.Cells(m + 3, 14) = ar(i + 2, 5)
m = m + 5
10:
End If
Next i
End With
End Sub
|