Create labels on one sheet from active row on another sheet

[Jeanette]

I have to produce labels, varying in size/shape (and number) on
"LabelSheet" and fill them with the contents of the active row on
"DataSheet". Explanation: UserForm pops up "Would you like the labels
at this time?", Yes, another UserForm pops up "Click on Entry (Row)
you want your label created from and then click OK" (is this bit
possible?). User clicks anywhere on Row, Row becomes ActiveRow. Then
on "LabelSheet" (which is hidden at this point) all the labels get
populated with the stuff in the ActiveRow on the "DataSheet" (copied,
not moved), "DataSheet" gets hidden and then "LabelSheet" is shown for
user to print. I should add, some labels only get one Cell Value
while other labels get more than one Cell Value, but if it makes it
easier I could design the labels so that one Cell Value on "DataSheet"
equals one Cell Value on "LabelSheet". I do hope someone out there
knows the answer, as I simply haven't got a clue!!! and all help will
be very much appreciated. BTW, merge fields is NOT an option!

 

[Otto Moehrbach]

You say:
"all the labels get populated with the stuff in the ActiveRow". This means
that "all" the labels will be the same. Is that what you want? And how
many labels is "all the labels"? Provide an example of what is in a row and
what you want the labels to look like after the copying is done. Detail
what is in what column before and after. HTH Otto

 

[Jeanette]

You say:
"all the labels get populated with the stuff in the ActiveRow".  This means
that "all" the labels will be the same.  Is that what you want?  And how
many labels is "all the labels"?  Provide an example of what is in a row and
what you want the labels to look like after the copying is done.  Detail





- Show quoted text -

Hi Otto
Thanks very much for replying. Two labels are identical and will get
populated from the value of one cell. Another two labels are
identical and will get populated from the values of four cells. One
label gets populated from the value of four cells but not the same
four as just mentioned. One label gets populated from the value of
five cells. The values come from a number of columns, but occupy only
one row on another sheet. If this sounds too confusing, would it be
easier if I emailed you the labels? It would be great if you could
help - I know what I need - just not how to get there!! Thanks in
advance.

 

[Otto Moehrbach]

Yes, do that. I'm totally confused. Include some samples of what you want.
Remember that a computer is totally dumb. It has to have some logic to work
with to determine what goes into one label. Show me that logic. My email
is (e-mail address removed). Remove the "extra" from this address.
Otto

You say:
"all the labels get populated with the stuff in the ActiveRow". This means
that "all" the labels will be the same. Is that what you want? And how
many labels is "all the labels"? Provide an example of what is in a row
and
what you want the labels to look like after the copying is done. Detail
what is in what column before and after. HTH Otto"Jeanette"





- Show quoted text -

Hi Otto
Thanks very much for replying. Two labels are identical and will get
populated from the value of one cell. Another two labels are
identical and will get populated from the values of four cells. One
label gets populated from the value of four cells but not the same
four as just mentioned. One label gets populated from the value of
five cells. The values come from a number of columns, but occupy only
one row on another sheet. If this sounds too confusing, would it be
easier if I emailed you the labels? It would be great if you could
help - I know what I need - just not how to get there!! Thanks in
advance.

 

[Jeanette]

Yes, do that.  I'm totally confused.  Include some samples of what you want.
Remember that a computer is totally dumb.  It has to have some logic towork
with to determine what goes into one label.  Show me that logic.  My email
is (e-mail address removed).  Remove the "extra" from this address..




Hi Otto
Thanks very much for replying.  Twolabelsare identical and will get
populated from the value of one cell.  Another twolabelsare
identical and will get populated from the values of four cells. One
label gets populated from the value of four cells but not the same
four as just mentioned.  One label gets populated from the value of
five cells.  The values come from a number of columns, but occupy only
one row on another sheet.  If this sounds too confusing, would it be
easier if I emailed you thelabels?  It would be great if you could
help - I know what I need - just not how to get there!!  Thanks in
advance.- Hide quoted text -

- Show quoted text -

My grateful thanks goes to Otto Moehrbach for the following code and
for sticking with me to find a solution. The labels were previously
in Word with Merge Fields. Now they're in Excel. What he did was to
keep the idea of merge fields on the labels, but changed them to Range
names instead. Then the active row on one worksheet could be "merged"
with the labels on another sheet. The resulting code he used was
this:

Sub MakeLabel()
Dim i As Range
Sheets("Labels").Visible = True
Set i = Cells(ActiveCell.Row, 1)
i.Name = "DeedNum"
i.Offset(, 1).Name = "Surname"
i.Offset(, 2).Name = "Forenames"
i.Offset(, 3).Name = "Address"
i.Offset(, 4).Name = "Account"
i.Offset(, 5).Name = "DateIn"
End Sub

It works like a dream - A HUGE THANK YOU Otto
Jeanette