批量制作价格牌标签

Public ar_Source As Variant
Sub Main()
With Sheets("Source")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
ar_Source = .Range("b4:f" & lastrow)
Call setcol
End With
With Sheets("Res")
split (ar_Source)
split2 (ar_Source)
End With
End Sub

Sub split(ar_index_)
With Sheets("Res")
k = 5
For x = 1 To UBound(ar_index_)
k = k + 1
If k Mod 5 = 1 Then n = n + 6: k = k - 4
.Cells(n, k) = ar_index_(x, 1)
setformat (n)
'.Cells(n + 1, k) = ar_index_(x, 2)
'.Cells(n + 2, k) = ar_index_(x, 3)
'.Cells(n + 3, k) = ar_index_(x, 4)
'.Cells(n + 4, k) = ar_index_(x, 5)
Next
End With
End Sub
Sub split2(ar_index_)
With Sheets("Res")
k = 5
For x = 1 To UBound(ar_index_)
k = k + 1
If k Mod 5 = 1 Then n = n + 6: k = k - 4
'.Cells(n, k) = ar_index_(x, 1)
'setformat (n)
.Cells(n + 1, k) = ar_index_(x, 2)
.Cells(n + 2, k) = ar_index_(x, 3)
.Cells(n + 3, k) = ar_index_(x, 4)
.Cells(n + 4, k) = ar_index_(x, 5)
Next
End With
End Sub
Sub setformat(r)
With Sheets("Res")
.Range(.Cells(r, 2), .Cells(r, 5)).Font.Name = "微软雅黑"
.Range(.Cells(r, 2), .Cells(r, 5)).HorizontalAlignment = xlCenter
.Range(.Cells(r, 2), .Cells(r, 5)).VerticalAlignment = xlCenter
End With
End Sub

Sub setcol()
For x = 1 To UBound(ar_Source)
ar_Source(x, 1) = "数字编号:" & ar_Source(x, 1)
ar_Source(x, 2) = "型号:" & ar_Source(x, 2)
ar_Source(x, 3) = "原价:" & ar_Source(x, 3)
ar_Source(x, 4) = "折扣:" & Round(ar_Source(x, 4) * 10, 1) & "折"
ar_Source(x, 5) = "现价:" & ar_Source(x, 5)
Next
End Sub

Sub biankuang()
For x = 6 To 1222 Step 6
bk (x)
Next
End Sub
Sub bk(x)
' Range("b6:b10").Select
Range(Cells(x, 8), Cells(x + 4, 8)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub