Dynamic Print Range for Template

I am working on a project where I need to take a data set, sort it onto individual tabs, and then print the data from the tab onto a sheet of labels. I have it figured out but there is one last piece of the puzzle that I am missing. I would like to be able to only print labels for the amount of rows I have.

Just to clue you in, I am VBA illiterate and probably work 10 times harder to get to the same result because of it.

Background:
I get a list of parts in excel that includes part numbers, description, storage location, etc.
list.JPG

I then would have to filter it by storage location and print it to give the person retrieving the parts. Re-filter for another loction....print... I created a workbook that allows me to copy and paste the data on a tab and then sorts the data into individual tabs and then I set a dynamic print range on each tab. Now I just paste my data set, click on the first tab, hold shift and click on the last tab and print. Bingo!
sort.JPG

Now they want me to instead print each tab onto individual labels for each part number.

I have the template for the label sheet set up in excel. I have the data automatically populate the label in the correct format by using Concatenate with links to the corresponding tab. I just cannot seem to be able to wrap my head around how to create a dynamic range to only print labels that I have data for and not a set number of labels each time.

label.JPG

The people that use this can barely do much more than the copy and paste so I am trying to make it fairly bomb proof. I would like to paste the data, click on a tab and print. If possible. I saw 'mail merge' but I am afraid that the extra step and additional program would create huge issues and I would be fighting fires all day.

Thanks!
Dave

Ok, like I said, I probably made way more work than needed to accomplish the task. The purpose of this is to take the raw data and paste it into the ‘MO file’ tab, enter some info into the few cells on that tab, and print out labels sorted by location in the format shown on the ‘LB Print’ tab. I only wish to print out as many labels as listed in that location. Every location will need its own separate sheet of labels since each location is a different person. I have the tabs set up for several hundred items each but truly, there will very seldom be over one hundred. Usually 50 or less for each location. The raw data can get into the hundreds.
I have listed an explanation of what each tab contains and what it is used for below to maybe help clarify my convoluted thought process. This is my second attempt of creating a workbook of this complexity so I am very much in the learning stages.

Thanks for taking a look!

Tabs-

Raw Data: This is an example of how the data will entered. They will copy and paste this from their sheet to the ‘MO file’ tab.


MO file: They input the Packet #, Job #, and Suffix #. The data is exported from our OS into an excel sheet (see example on the ‘Raw Data’ tab). They copy and paste the section of that export into this sheet (everything under the yellow cells). This list changes with every job. The ‘Seq’ column is not used for this but is included for ease of copy and paste. The other tabs have a ‘Sequence’ column but there is no correlation.


LB, LR, Yard, G Supermarket, Q Supermarket, Catch-all, ASSOCWH, SOMCABB, RO2: These are the separate locations that the raw data needs to be sorted to. They refer to the letter designation at the beginning of the location. These lists, (plus Packet #, Job #, and Suffix # from the MO file tab) are what will need to be printed out on individual label sheets for each location. I have these set up dynamically since they used to hold down ‘Ctrl’ and pick which ones they needed and print them.


LB Print: This is the template that I have made to match the sheet of labels. I used concatenate to gather all of the data and put it into the format desired for each label. Eventually these will be arranged by the location (the letters and numbers following the initial letters used to create the tabs) for ease of gathering parts. This is what I would like to have a dynamic range for so I do not have to use 100 labels each time just to get a list that only has 15. There will ultimately be a separate tab for each location for the labels just like there is for the sorting. If VBA can do this without needing the tabs, that is fine. I just was making them for my benefit.


MO arranged: This tab is used to re-arrange the original data list to allow to use VLOOKUP to sort by the ‘item’ column. It also is used to sort out which stocking location each item is in (columns J-Q)and then columns R-U are used to determine any stragglers to be put into the ‘Catch-all’ tab. Basically, I look to see if there is a location designation in columns J-Q if not then I return a ‘0’ (column R), then I look for data in columns B-E if there is I return a ‘0’ (column S). I add R and S in column T and if it is ‘0’ then I put a ‘C’ in column U to designate the Catch-all tab.


Master sequence file: This data set holds all of the locations (sequences) of where the parts go. Columns B-D are the original data. MOs (Packets) are individual work centers so an item may not always go to the same place. This tab I use to determine the sequence number needed by using the Packet # entered on the ‘MO file’ tab.

Originally Posted by 6StringJazzer

Welcome to the Forum Ironhead Dave!

I could probably do a simple VBA solution for you but I would need to see your actual file as shown above to test it. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Hi Jeff,

I wanted to check if you are able to make heads or tails of my file. Let me know if you have any questions.........Besides "What in the #3LL were you thinking?!?!" Ha Ha

I am not going to try to redesign your entire solution, it's just too big for me to analyze it. I am focusing on how to set a print area in LB Print that is limited to the cells that actually have data. But LB Print in your file does not look like the image you posted above. There are formulas only in a few cells in the first column. How do you populate this sheet for printing?

Originally Posted by 6StringJazzer

I am not going to try to redesign your entire solution, it's just too big for me to analyze it. I am focusing on how to set a print area in LB Print that is limited to the cells that actually have data. But LB Print in your file does not look like the image you posted above. There are formulas only in a few cells in the first column. How do you populate this sheet for printing?

I do not expect you to redesign the whole thing. I thought it might be easier for you to follow if I gave you an explanation of where it all comes from. The file I sent you is a dummy file so that I did not send work related data out on the net. The formulas in LB Print are just not carried through to all of the cells on that file because I could not get it to repeat easily with the blank columns and had to manually modify every one. I can send you one with all of the formulas if you need it.

The way it works now is it pulls some of the info off of the 'LB' and some off of the 'MO file' so the cells will always have something in them like this when the list runs out on 'LB'.
Print empty..JPG
I would need to use the number of items in 'LB' to dictate the number of labels and not the actual cells on the 'LB Print'

Now I can set this up anyway i need to. Would it be easier if I did the CONCATENATE in the 'LB' and just linked the cell in 'LB Print' to that? That should give you only cells with data on the print sheet.

Let me know w

The only thing I need to know is in what order the label cells get populated. That is, do you fill up the data by columns, or by rows? The image looks like you do it by rows but the attached file makes it look like columns. I assume you fill the first page before starting on the second page.

The order does not matter. I would prefer rows, but truly it makes no difference. What ever is the easiest. Yes. I will fill the first page before moving to the second.

Thank you!

But the way you build the data dictates what solution I provide. To avoid going in circles, I will provide code that prints all pages that have any cells with data.

Because you are printing labels, you are going to have to print a whole page even if there is only one cell with data; that is, you can't just select the cells with data for printing, you have to select an entire page to get the alignment with the labels correct. I have put code in the sheet LB Print that selects the pages for printing. The code in Module1 actually prints the labels. You will have to decide how you want to invoke that. I was going to add a button but I didn't see a good place to put it.

Also I changed the codename of LB Print to LBPrint so it is readable in the code.

Great! Thank you! I will let you know if I have any problems figuring it out. I have used VBA before but just copied stuff off the internet.

OK. All you have to do is run the macro PrintLabels when you want to print. If you need more details let me know.