accessing avery label table in macro

Guessed · #1 08-07-2022, 10:21 PM

Try this code. It assumes your cursor is in a cell you have already populated and then asks for how many tickets to add. It makes sure there are enough cells remaining in the table and then duplicates the current cell contents into the required cells.

Code:

Sub AddTickets()
  Dim i As Integer, iCount As Integer, celSrc As Cell, celAdd As Cell
  Dim rngCell As Range, rngToEnd As Range, aTbl As Table
  Set aTbl = Selection.Tables(1)
  Set celSrc = Selection.Cells(1)
  Set rngCell = celSrc.Range
  rngCell.MoveEnd Unit:=wdCharacter, Count:=-1
  Set rngToEnd = rngCell.Duplicate
  rngToEnd.End = aTbl.Range.End
  iCount = InputBox("How many tickets do I need?", "Ticket Copier", 2)
  If iCount > 1 Then
    Do While rngToEnd.Cells.Count < iCount
      aTbl.Rows.Add
      rngToEnd.End = aTbl.Range.End
    Loop
    Set celAdd = celSrc.Next
    For i = 2 To iCount
      celAdd.Range.FormattedText = rngCell
      Set celAdd = celAdd.Next
    Next i
  End If
End Sub

littlepeaks · #2 08-09-2022, 12:10 PM

Thanks --

I tried that, but got a runtime error 91 on the line

celAdd.Range.FormattedText = rngCell

It says: cel.Add.Range.FormattedText: = <Object variable or Width block variable not set>

rngCell did pick up the text from the first cell, so I'm not sure what's going on.

Anyway, my question was solved, and I am using a simpler macro to do what I want it to do. I will probably use an input box, or form for input in addition to my code, after I decide exactly what I want to do. My basic code to populate all the labels:

Sub Copy_Labels()
Dim strLabelText As String
Dim a As Long 'Row
Dim b As Long 'Column

Application.ScreenUpdating = False
strLabelText = ActiveDocument.Tables(1).Cell(1, 1).Range.Text
For b = 1 To 7 Step 2
For a = 1 To 20
ActiveDocument.Tables(1).Cell(a, b).Range.Text = strLabelText
Next a
Next b
Application.ScreenUpdating = True

End Sub

I found out that in columns, there is a column of blank cells between each column of Avery Labels.
Also turned off screen updating, because the macro was going kind of slow.

Thank-you everyone for your input.

littlepeaks · #3 08-09-2022, 12:12 PM

I'm sorry -- my forum response did not pick up the code formatting (indents, etc).

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Saving Avery Label 8376	fsinclaire	Word	1	04-30-2022 09:12 AM
Accessing the format tab with the macro recorder	reneemettrie	Word VBA	6	01-10-2020 12:03 PM
Avery Label Templates, editing rows/spacing	rainandwind_95448	Word	2	12-13-2015 08:05 AM
Avery Label Template for Word 2003?	Clueless in Seattle	Word	2	03-31-2015 06:15 AM
Having trouble using Avery 4014 label to print multiple pages	Dickie	Mail Merge	1	09-29-2013 07:28 PM