Printing labels, automatically add "END" macro

pavimeus

New Member
Joined
Nov 4, 2002
Messages
45
I work for a list management company. We print A LOT of mailing labels for our clients. The type of labels we use is perforated for easy tear, 3 across, 12 down, 36 to a page (where the perforations begin), and 15,000 to a box.

It is far more time efficient to print multiple “orders” at a time, using the entire box (15,000 labels), rather than printing one “order” at a time, per client. The issue is, how do we separate each batch of orders from 15,000 labels, that is going to X no. of different clients, once the entire print job is done. We could flip through the pages and look for order numbers, and go to work with a pair scissors.

Too much work.

So here’s what we are doing. All the labels are mail merged into a Word document, from an Excel spreadsheet. There is an order no. at the start of each “order”. We can flip through the pages and look for this order no. so that we know where to begin. On the end of the excel spreadsheet for each order, we insert a series of cells with the word “END”, until the total number of rows, per order, is divisible by 36.

For example, if we have an order calling for 1136 labels (each label constitutes one row of data on the spreadsheet), then we will add 16 additional cells with the word “END” so that the total equals 1152, which is divisible by 36. Recall that 36 is where each perforation begins. So once the print job is done, we look for the order no. (so that we know where to begin), and then a series of “END”s, and if done correctly, it will tear right at the perforation. Then we go on to the next order, so on a so forth.

It works, but a bit tedious.

After all the rambling above, here, finally, is my call for help. Could you good folks out there write a macro that automatically does the calculations and inserts the required no. “END”s, for each order? (The “END”s have to be inserted at the top of the spreadsheet). Then we can copy and paste the portion on to another spreadsheet, until we max out 15,000 rows, therefore allowing us to run several orders per box, all at once.

I hope I have explained things well enough. Thank you very, very much for your feedback. Any all thoughts welcome.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try the following:

Sub InsertEnds()

Dim lngRowCount As Long
Dim lngInsertNumber As Long
Dim lngInsertCounter As Long

Range("A65536").Select
Selection.End(xlUp).Select
lngRowCount = ActiveCell.Row
lngInsertNumber = 36 - lngRowCount Mod 36
If lngInsertNumber > 0 Then
For lngInsertCounter = 1 To lngInsertNumber Step 1
Range("A1").Insert shift:=xlDown
Range("A1").Formula = "END"
Next lngInsertCounter
Range("A1").Select
End If

End Sub
 
Upvote 0
BRILLIANT!! Thank you Artemus.

A slight tweak is needed.

Right now the spreadsheet looks like this after the macro is run;

END First Name MI Last Name Suffix Street Address City State ZIP Code
END Order No.
END Name1 A Surname1 Jr 123 Any Street Anytown XY 12345-6789
END Name2 B Surname2 124 Any Street Anytown XY 12345-6790
END Name3 A Surname3 125 Any Street Anytown XY 12345-6791
END Name4 B Surname4 III 126 Any Street Anytown XY 12345-6792
END Name5 A Surname5 Sr 127 Any Street Anytown XY 12345-6793
Title Name6 B Surname6 128 Any Street Anytown XY 12345-6794
Name7 A Surname7 129 Any Street Anytown XY 12345-6795
Mr. Name8 B Surname8 IV 130 Any Street Anytown XY 12345-6796
Ms.
Mrs.
Dr.
Mr.
Ms.
Mrs.
Dr.



It should be

END
END
END
END
END
END
END
Title First Name MI Last Name Suffix Street Address City State ZIP Code
Order No.
Mr. Name1 A Surname1 Jr Anytown XY 12345-6789
Ms. Name2 B Surname2 Anytown XY 12345-6790
Mrs. Name3 A Surname3 Anytown XY 12345-6791
Dr. Name4 B Surname4 III Anytown XY 12345-6792
Mr. Name5 A Surname5 Sr Anytown XY 12345-6793
Ms. Name6 B Surname6 Anytown XY 12345-6794
Mrs. Name7 A Surname7 Anytown XY 12345-6795
Dr. Name8 B Surname8 IV Anytown XY 12345-6796




Perhaps

Range("A1").Insert shift:=xlDown
Range("A1").Formula = "END"

A1 should be "A:A"?

Thank you again.
 
Upvote 0
Try this one:

Sub InsertEnds()

Dim lngRowCount As Long
Dim lngInsertNumber As Long
Dim lngInsertCounter As Long

Range("A65536").Select
Selection.End(xlUp).Select
lngRowCount = ActiveCell.Row
lngInsertNumber = 36 - lngRowCount Mod 36
If lngInsertNumber > 0 Then
For lngInsertCounter = 1 To lngInsertNumber Step 1
Selection.EntireRow.Insert
Range("A1").EntireRow.Insert
Range("A1").Formula = "END"
Next lngInsertCounter
Range("A1").Select
End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top