Need on advice on automating Excel Table that autofills labels on second sheet of disposal log.

Copper Contributor

Hi all,

I am looking for advice on how to improve a disposal log that tracks all items that get broken in one day at work by customers. The first sheet is has all the usual company logos and headers at the top with 20 rows underneath to fill in details for each product. The details are product code, name, price, quantity, and brief desscription on what's wrong with it. This page is designed to be printed in landscape form. All this info needs to be typed in by hand.


The second sheet is formatted to fit labels. There's 2 labels per column and 7 rows with the cells and borders all aligned to fit these printable label pages, which means there's definately a lot of work involved if there is a slight change in label used. Also, since there are 20 rows for items on the first sheet, one page of labels has 6 remaining that are unused. Sometimes, multiple pages are needed, so you need to clear the rows and type the new products.


The label's cells are hard coded to cells on the first sheet to autofill, which is very difficult to expand. I would need to create a lot of pages with hard coded data to make an even number (LCM(14,20) = 140). Also, if I made hard coded pages, then on print you would need to select the pages you wanted.


Most of the data that we need to enter is in another Excel book -- the warehouse list. All items in the warehouse are logged in and broken items have their own location. The easy part would be to use Power Query to pull all the needed info into the disposal log and fill in the first page and then use repeat rows to make each page print the needed header stuff.


The advice I need is what to do about the second sheet? It is not uncommon to have 2 or more pages of broken items (I know it's a lot). At Christmas time, there can be a lot of pages as Christmas products are all breakable and customers and kids... we can have upwards of 10 or more pages. In order to satisfy Christmas, I would need to try to expand the labels page to the most amount we would ever need. Is there a better way of generating labels? It would need to be easy for the office staff to use. Hence the current method of just switching to a new sheet and printing. I already plan on using a button on the first page to refresh the query against a new copy of the warehouse list. Is there a macro to generate all the labels we need, then no selecting the number of pages they want? If it helps, the office computers have Excel 2016.


Many thanks.

3 Replies
That looks more like commissioned work and not like a simple help to get ahead, hope to find someone who can invest so much time ... whatever
it would be helpful for all of us if you could add a file (without sensitive data). This way you can hope for an answer faster and we can offer you an answer faster, as far as possible.

I know I don't know anything (Socrates)

@NikolinoDE Thanks for your reply. I wish to learn what to do to improve my skills, which is why I seek guidance in how I should proceed and hopefully pointers to a tutorial. I cleaned the file, so hopefully there's no sensitive information in it.


On the first sheet, I plan to remove the table part and replace that with a Power Query formatted table getting all the data from an outside Excel file that has all the needed info. It's just I'm not too sure what to do about the second sheet of labels.


Note: I edited this at home where I have Excel 2019, but the office has Excel 2016. I tend to work from home from time to time (it's safer).


Many thanks.

Since this is about pivot, Mr. @Sergei Baklan would be best suited. That means that everyone else here isn't exactly as good. I can assure you they are definitely better than me. It's just Mr. @Sergei Baklan specialty.
Hope that this is OK for you. Mr. @Sergei Baklan :) ...and are not angry with me.

I know I don't know anything (Socrates)