Hi
I use a macro to give me a set of custom labels which I can print out. I've attached a WORKING SAMPLE of my SS which demonstrates my macro working.
My macro gets selected data from "LabelData" sheet and populates the A4 printable "LabelTemplate". The macro populates the first A4 sheet with 8 labels, then the next A4 sheet below it, and so on...
I now need to use multiple "LabelData" sheets (eg. "LabelData", "LabelData2", LabelData3") and populate the same A4 "LabelTemplate" with the selected data...
Existing macro;
Code
' Print Labels from Detail List
Sub Labels()
Call AllWorksheetPivots
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim r As Integer
Dim c As Integer
Dim p As Integer
Dim r1 As Integer
Dim r2 As Integer
Dim r3 As Integer
Dim r4 As Integer
Dim i As Integer
Dim c1 As Integer
Set sh1 = Sheets("labelData")
Set sh2 = Sheets("LabelTemplate")
'sh2.Cells.MergeCells = False
sh2.Cells.ClearContents
r = 0
c = 1
p = 1
For i = 4 To 5000
If UCase(sh1.Cells(i, 16)) = "L" Then
r = r + 1
If r > 4 Then
r = 1
c = c + 1
End If
If c > 2 Then
p = p + 1
r = 1
c = 1
End If
If r = 1 Then r1 = 3 + (53 * (p - 1))
If r = 2 Then r1 = 6 + 9 + (53 * (p - 1))
If r = 3 Then r1 = 8 + 18 + (53 * (p - 1))
If r = 4 Then r1 = 11 + 27 + (53 * (p - 1))
If r = 5 Then r1 = 14 + 36 + (53 * (p - 1))
If r = 6 Then r1 = 17 + 45 + (53 * (p - 1))
If r = 7 Then r1 = 20 + 54 + (53 * (p - 1))
If r = 8 Then r1 = 23 + 63 + (53 * (p - 1))
If c = 1 Then c1 = 3
If c = 2 Then c1 = 20
sh2.Cells(r1 + 1, c1) = sh1.Cells(i, 7)
sh2.Cells(r1 + 1, c1 + 11) = sh1.Cells(i, 9)
sh2.Cells(r1 + 2, c1) = sh1.Cells(i, 8)
sh2.Cells(r1 + 3, c1) = sh1.Cells(i, 10)
sh2.Cells(r1 + 4, c1 + 1) = sh1.Cells(i, 11)
sh2.Cells(r1 + 4, c1 + 11) = sh1.Cells(i, 12)
sh2.Cells(r1 + 5, c1 + 5) = sh1.Cells(i, 13)
sh2.Cells(r1 + 5, c1 + 11) = sh1.Cells(i, 14)
'sh2.Range(Cells(r1 + 5, c1 + 11), Cells(r1 + 5, c1 + 13)).MergeCells = True
End If
Next i
'Stop
'sh2.PrintOut
sh2.Select
Cells(1, 1).Select
End Sub
Display More
Many thanks.
PS, I paid the 10% to OzGrid before posting...