Address to Sticker Layout in Excel Macro Code

Address to Sticker Layout in Excel Macro Code

Sub GenerateStickersMultiPage()

    Dim wsData As Worksheet, wsSticker As Worksheet

    Dim i As Long, r As Long, c As Long

    Dim addrRow As Long, stickerNo As Long

    Dim lastRow As Long, pageNo As Long

    Dim startAddr As Long

    

    Set wsData = ThisWorkbook.Sheets("Sheet1")

    

    ' Delete old sticker sheet if exists

    On Error Resume Next

    Application.DisplayAlerts = False

    ThisWorkbook.Sheets("Stickers").Delete

    Application.DisplayAlerts = True

    On Error GoTo 0

    

    ' Create new sticker sheet

    Set wsSticker = ThisWorkbook.Sheets.Add

    wsSticker.Name = "Stickers"

    

    ' Find last row of data in Sheet1

    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    

    stickerNo = 1

    pageNo = 1

    

    ' Loop through all addresses

    For addrRow = 2 To lastRow

        ' Calculate row & column for this sticker

        r = ((stickerNo - 1) Mod 24) Mod 8 + 1

        c = Int(((stickerNo - 1) Mod 24) / 8) + 1

        

        ' Calculate target row with page offset

        targetRow = r + (pageNo - 1) * 10   ' 10 = space between pages

        targetCol = c

        

        ' Place address in sticker cell

        wsSticker.Cells(targetRow, targetCol).Value = _

            wsData.Cells(addrRow, 1).Value & vbNewLine & _

            wsData.Cells(addrRow, 2).Value & ", " & wsData.Cells(addrRow, 3).Value & vbNewLine & _

            wsData.Cells(addrRow, 4).Value & ", " & wsData.Cells(addrRow, 5).Value & " - " & wsData.Cells(addrRow, 6).Value

        

        ' Format

        With wsSticker.Cells(targetRow, targetCol)

            .WrapText = True

            .HorizontalAlignment = xlCenter

            .VerticalAlignment = xlCenter

            .RowHeight = 50

        End With

        wsSticker.Columns(targetCol).ColumnWidth = 25

        

        ' If 24 stickers are filled → new page

        If stickerNo Mod 24 = 0 Then

            pageNo = pageNo + 1

        End If

        

        stickerNo = stickerNo + 1

    Next addrRow

    

    MsgBox "Stickers generated for " & (lastRow - 1) & " addresses in " & pageNo & " pages.", vbInformation

End Sub