Creating part # tags using sequential numbering.

[Andrew M.]

Very simply, my question is this...

I want to try to use M EX to create tags for the parts we make during the
manufacturing process. The tag would have some standard information about
the part on the tag...

The problem being that I have a formula (that works) for creating the
sequential next tag # (0001, 0002, 0003, 0004...etc). The problem is that we
have hundreds of boxes and that it would take days/weeks to manually make a
tag for each box.

Now I've tried using copy/paste but the problem is that the formula
constantly looks for information in the wrong cells and that means manually
changing the tag # cell of each tag manually. (I have to change 4 different
cell references per tag).

Is there an easier way to do this?

Each tag takes up a space the size of A1-G12. The tag # reference cell is G8.
The formula I've been using is "=IF((G21-G7=1), G21+1, G21+2)" with the
first tag ref # being 1000 and the second being 1001.

The tag # always references the 2 tag # before it.

Any help with this is greatly appreciated.

Sincerely,
Andrew M.

 

[Dave Peterson]

Instead of using excel as the form, you may want to use excel to hold the data,
but create the form in MSWord.

Then you could use MSWord's mailmerge capability to print those labels.

You may want to read some tips for mailmerge.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

If that doesn't appeal to you, I think I'd put the "master" form in A1:G12.
Then put the next label in A13:G24.

But make all the formulas point at the form in A1:G12. Only use absolute
references for the fields that won't change--titles and that kind of thing.

But for the tag numbers, you could use =G8+1 (and give it a custom format of:
0000 (to preserve those leading 0's)).

Then you could have a macro that would copy that label (in A13:G24) to A25:G36
and A37:g48 and....

It could even insert page breaks every 3 or 4 (or whatever) labels.

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim HowMany As Long
Dim HowManyPerPage As Long
Dim iCtr As Long
Dim wks As Worksheet
Dim DestCell As Range

Set wks = Worksheets("Sheet1")
HowMany = 50 'while testing
HowManyPerPage = 3

With wks
Set RngToCopy = .Range("a13:g24")
Set DestCell = .Range("a25")

.ResetAllPageBreaks

For iCtr = 1 To HowMany
RngToCopy.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count)
If (iCtr + 2) Mod HowManyPerPage = 0 Then
.HPageBreaks.Add Before:=DestCell
End If
Next iCtr
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Andrew M.]

Thx for the input. One of my coworkers knows how to use mailmerge and I'm
going to figure out the macro and we'll be a joint effort for a common cause.
We appreciate the quick response.

Best Wishes,
Andrew M.
Tylok International