|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
留意 Const RowsBetweenLabels = 3 & Const LabelsInOnePage = 15, 就是調節行之间及每頁labels數量
- Sub zz()
- Dim n&, m&, MyLabel, r, c, rng As Range, rc(1), pb&, tbr&, tbc&, labels$
- Application.ScreenUpdating = 0
- Const RowsBetweenLabels = 3
- Rem LabelsInOnePage 3 columns of labels x rows of lables
- Const LabelsInOnePage = 15
- Set rng = Sheets(1).[a1:d3]
- tbr = rng.Rows.Count + RowsBetweenLabels
- tbc = 5
- MyLabel = rng.Value
- c = Split("2|2|2", "|")
- r = Split("1|2|3", "|")
- a = Sheets(2).[a2].CurrentRegion
- Sheets(1).Copy after:=Sheets(Sheets.Count)
- For i = 2 To UBound(a)
- If a(i, 5) Then
-
- For j = 1 To a(i, 5)
- For n = 0 To UBound(c)
- MyLabel(r(n), c(n)) = a(i, 2 + n)
- Next
- rng.Copy Cells(rc(0) * tbr + 1, rc(1) * tbc + 1)
- Cells(rc(0) * tbr + 1, rc(1) * tbc + 1).Resize(3, 3) = MyLabel
- m = m + 1
- rc(0) = Int(m / 3)
- rc(1) = m Mod 3
- pb = pb + 1
- If pb Mod LabelsInOnePage = 0 Then ActiveSheet.HPageBreaks.Add Before:=Cells(rc(0) * tbr + 1, 1)
- Next
- End If
- Next
- Application.ScreenUpdating = 1
- End Sub
复制代码
|
|