I downloaded an Avery Label Word template. I want to write a macro to fill in multiple labels, based on my choice with the same text.





I understand that the template is a table. How do I access the table in VBA so I can manipulate cell/row/column properties, etc?


Sorry -- I'm not a VBA expert, but this isn't as simple in Excel.

We don't know what kind of manipulations you require. Working with tables is different to Excel but I wouldn't say it is particularly complex. You can work at the Range, Table, Row, Column or Cell levels so we would need to know what it is you want to do to the table.

You can start by recording a macro of you using the GUI to make those manipulations. Once you have some starting code, you can post it here and someone can tidy it up for you.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

I want to copy the text from cell and paste it into other cells, as selected. I tried recording a macro, but when I copied the info from one cell, selected the next cell, and pasted, it just used an offset. And sometimes, the recorded macro didn't even run correctly. We are a nonprofit, and have a yearly raffle. For those who buy 20 or more tickets, we print out labels for them to paste on the tickets for the drawing, so they don't have to fill them out by hand. We use 1 sheet for several customers -- for instance I might have the first 20 going to one and another 25 labels going to another, etc. I would like to copy the data from cell into other selected cells. I could do this by using a loop, or using something like table.Cell(2,2).Range.Text = strTextCopied, but I can't seem to reference the table. I'd just like to try this as a learning experience. (TMI?)

Perhaps you should consider using mailmerge. No vba required.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Are you printing these pages out and cutting them up to put in to a barrel for the draw? It sounds awfully labour intensive and likely not fully random as there would be variation in paper slip sizes.

Have you considered using an electronic method to quickly store ticket holder names and do the randomised draw - I presume there are smartphone/ipad apps that do exactly this.

If you wanted to stay in Word and manually cut out paper, a macro could be written to ask the user (ticket seller) for how many tickets and repeat this that many times.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

The tickets have an area for the purchasers' info. To save the purchasers the hassle of filling them out manually, we give them a sheet of printed labels. They peel them off and stick them on the part of the tickets that go into the barrel. The randomness is the manual drawing of the tickets from the barrel. Using an electronic method would be scary -- if something went wrong, I don't know what we'd do. And we are licensed by our state to do this. We are a small nonprofit -- only volunteers -- no paid employees.

I've used mail marge once or twice -- not sure what the limitations of its capabilities are. More familiar with VBA.

Never mind-- I opened up the VBA editor in Word , and looked up "Tables" in the "Help", "Word Object Model Reference". It told me what I wanted to know.


Basically I can use:


strLabelText = ActiveDocument.Tables(1).Cell(1,1).Range.Text 'Copies the text
ActiveDocument.Tables(1).Cell(2,1).Range.Text= strLabelText 'Pastes the text into the cell below


This requires a lot more coding and cleaning up, but I could not figure out how to access the table. I have something to work with now.


Thanks again.

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.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

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.

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