|
#1
|
|||
|
|||
Select next available mailing label by row and column?
Has any one worked out a way, without resorting to Word, to get Excel to print a mailing label by inputting the row and column of the next available label on a standard Avery 14 row / 2 column label sheet.
The addressees are all contained on a single table and would like to be able to print a single 'to' and a single 'from' label. I've done a forum search and nothing pops up... Last edited by kiwimtnbkr; 10-16-2020 at 12:44 PM. Reason: Expand explanation |
#2
|
|||
|
|||
So you want to print 1 address multiple times? Do you have an example?
|
#3
|
|||
|
|||
No - it would one 'to' address label and one 'from' address label printed on demand. Some suppliers get sent to on a regular basis but others only two or three times a year.
No example at this stage as it's only a conceptual idea at this stage, although if it proves doable then I will look to incorporate it into an existing project in order to have a 'one stop shop'. Can mock an example up if you think it might explain things a bit better? |
#4
|
|||
|
|||
i am sure something couldnt be done, but i cant really visuallise exsactly what you are after
|
#5
|
|||
|
|||
I've 'cheated' and quickly mocked it up using Word's 'Mailings - Create - Labels' and then pasted it Excel as a picture.
Hopefully this allows you to visualize what I am trying to say - picture worth a thousand words |
#6
|
||||
|
||||
If you're trying to do this via mailmerge, see: https://www.msofficeforums.com/73079-post6.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
The attached workbook might assist better in seeing if what I want to do is actually doable in the first place. Note that the 'ToFrom' sheet is protected but there is no password, you should be able to just 'unprotect' it.
When the user form opens (and the guts of the code to make it run is attributable to @Guessed - thank you Andrew!), the 'To' and 'From' fields are populated with Column A data from the table found on the 'Inner Envelope' worksheet. The code then pulls the matching data from Column B for the 'To address' on the 'ToFrom' worksheet and the matching data from Column C for the 'From address' on the same worksheet. A play with the user form will make up for the poor explanation above... What I would like to see, if it's doable, is when the tick box in the 'outer packaging' frame is selected and the row and column number of the next available blank label is input (see the outer envelope worksheet which I have done up to represent a sheet of Avery address [L7183] labels) is that the matching 'To address' data is pulled from the appropriate row of Column D and placed in the 1st available blank label and that the matching 'From address' data is also pulled from the appropriate row of Column D and placed in the 2nd available blank label. End game is that the Row x / Column B 'To address' on the 'ToFrom' worksheet matches the Row x / Column D 'To Address' filled in on the first selected blank label and that the Row x / Column C 'From Address' on the 'ToFrom' worksheet matches the Row x / Column D 'From Address' filled in on the second blank label. All of the individual shapes on the 'outer envelope' worksheet have been given an Alt Text name of 'OutLabeln' where 'n' is their equivalent label number on the Outer Envelope worksheet - if that helps with coming up with a solution. NOTE - The individual shapes are there more for the visual effect and could be deleted if necessary to leave just the cell which is sized to the same size as the actual label. Quote:
Last edited by kiwimtnbkr; 10-17-2020 at 02:56 PM. Reason: edited for clarity |
#8
|
|||
|
|||
I am not complete up to speed on your form but had a quick play and i think you could add somthing like
Code:
row = Me.TextBox1 col = Me.TextBox2 Call labels then in a new sub Code:
Sub labels() Worksheets("ToFrom").Shapes.Range(Array("ToAddressee")).Select Selection.Copy If col = 1 Then Worksheets("Outer envelope").Range("a" & row).PasteSpecial If col = 2 Then Worksheets("Outer envelope").Range("b" & row).PasteSpecial End If End If End Sub You have created the addresses in a shop, so i think you will have to play with sizes to make it all fit but this does the first copy and paste |
#9
|
|||
|
|||
I've copied the code into the project but I'm not seeing anything happening on the outer envelope worksheet.
I've added some text on the 'Inner envelope' and mocked up on the 'Outer envelope', what address would go where if Row 1 / Column 1 (or should that actually read column A) was input in the UserForm to clarify my poor attempt at explaining what I am hoping can be done. Would it be better to (is it even possible?), once the address has been copied from Inner envelope Column D, then it is pasted in as an 'Edit Text' for the applicable shape? Or would it be better to remove the shapes altogether and paste to the address into the required row / column cell? I am wondering if it would be easier to just say that the To address will always go in column A and that the From address will always go into column C (with column B being the spacer between the labels)? I am now rambling and just throwing ideas out there to people who are WAY more knowledgeable on what is possible with Excel. |
#10
|
|||
|
|||
All this is doable without any programming. What you need, is:
1. A way to determine for which companies are labels printed in session. E.g. a column in table on Inner Envelope sheet, where you select from Data Validation List TRUE, or "X" or "print" or whatever; 2. A hidden sheet with table, where Where and To addresses for marked companies are collected (1st marked company, 2nd marked company, etc. You have to estimate max possible number of marked companies, and you need a matching number of rows prepared for this table; 3. Some cell, where you can determine the row of 1st label sheet the addresses must start (i.e. to pass rows where labels are peeled off); 4. A report sheet, with table where 2 columns cells set to match label dimensions, and additional columns (and rows) to ensure the needed spacing between labels. Hard Page must be used to ensure only right number of label rows - it's 7 as I can see from your example workbook - is printed to every label sheet). This table must have enough rows for max number of marked labels even when on 1st label sheet printing starts from last row; 5. In Report Sheet, you use worksheet formulas to read From and To addresses starting from row you marked as starting one for 1st label sheet; 6. You have to define a dynamic Named Range, which includes all and only pages on Report Sheet, where are labels to print; 7. You define a Print Area for Report sheet, and set it equal to Named Range you defined. It's all! |
#11
|
|||
|
|||
i forgot you need the varible declaration at the top
Public ROW As Integer, COL As Integer I then clicked om, create new label, selected Label 1, Marking 1 and ticked the outer address box then 3 in row and 1 in column As i said i am not familar with your form or the code, so i assume there is a reason you went for a shape rather than text? Text seem more obvious to me but you call. |
#12
|
|||
|
|||
The shapes are there more for the visual effect and so I can make sure they are the right size when compared to the real label sheet.
If I understand you right, you're saying that I would be better getting rid of the shapes and just leaving the cells on the outer envelope? What, if any, changes would needed to insert just the text as you suggested? |
#13
|
|||
|
|||
Have had a chance to have get the code in the right places - should have clicked that having 'Call labels' was meant to be in a module. I originally had it in the UserForm code...whoops!
Couple of things that I hope you can assist with resolving... the 'to address' for the outer envelope label is getting picked up from the ToFrom worksheet when it should be getting picked up from Column D on the Inner Envelope worksheet. Selecting Column 1 works fantastic but nothing appears if Column 2 is selected? And there is no 'from address' appearing on the outer envelope label? Have reattached an updated worksheet with the coding you have kindly assisted with so far. |
#14
|
|||
|
|||
I must admit i am no expert when it comes for forms, never really had the need. I dont mind trying to help, but please bear this in mind
Quote:
Quote:
Code:
If Col = 1 Then Worksheets("Outer envelope").Range("a" & Row).PasteSpecial Paste:=xlPasteValues End If If Col = 2 Then Worksheets("Outer envelope").Range("b" & Row).PasteSpecial Paste:=xlPasteValues End If Quote:
|
#15
|
|||
|
|||
Thank you! that did the trick for that bit - and I now have a much better understanding of how and where to use End If statements
And awesome help with steering me in the right direction for the rest of it - very much appreciated indeed as I will tinker to get the rest of it working now that I know what code it needs and where to look for it |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to select multiple criteria from same column? | LearnerExcel | Excel | 1 | 03-10-2018 07:41 AM |
move the milestone label to left of column | wilster31 | Project | 0 | 07-31-2017 07:58 AM |
Error message in pivot when i try drag and placing a column in the row label | olybobo | Excel | 0 | 05-10-2016 02:05 PM |
A non mailing label? | markg2 | Mail Merge | 1 | 12-22-2011 04:07 PM |
Mailing label help needed... | Lisa Cotton | Word Tables | 1 | 04-14-2011 04:49 PM |