Macro to automatically workout number of labels and pages numbers

So, as per attached file, I have list of stores in DATA worksheet. This whole page will be pasted into worksheet every time the sheet will be used.
I want this macro to tell me how many labels I need to print � I need 1 label per 25 cases, so for example if I have 51 cases ordered per store I will need 3 labels printed. That will increase with every 25 cases that store will order.
My labels are saved in PDF file (1 store per page per label) so I don�t need to know which store number it is but page that this store is on. Number of pages are represented by small digits in row 8, 11,14 for example. I need this macro to return information:
1. about how many labels I need for every store (given it�s 1 label per 25 cases)
2. number of page it is on needs to be multiplied in the same cell separated by comma
3. all of the numbers from all stores needs to be consolidated in one cell, separated by comma.
4. PDF can only allow 99 characters to be put in pages print option so it would be good if this string of data would be separated into strings of no more than 99 characters.
5. This is to be done in Summary tab after pressing the button.
Could anyone be of any help with this?

Hi sintek,

Just General formatting, number separated by commas.

Hi again,

Please see attached for format I would like to use. Button should be set up for execution the macro.

"all of the numbers from all stores needs to be consolidated in one cell, separated by comma."

By this is mean - I need this macro to return to me page numbers that the label is on and needs to be printed also repeated as many times as needed. Page numbers are in line 8 for instance.

For example I need to print 2 labels for store 17, 3 labels for store 26 and 4 labels for store 258 (data from spreadsheet).

Then string that should be pasted into D9 cell in Summary tab should be: 1,1,2,2,2,3,3,3,3.

I understand it is confusing to understand as I find it very confusing to explain. Does this make more sense now?

Hi sintek,

Thanks for your help with all of these functions!

I will mark the thread when it's ready however today's day brought some more challenges. I have successfully used the macro in one of the instances I needed it for, however I need to amend some of it.

So, I have realized that number of my stores will be increasing (not sure to what number) hence I would need the file to be able to accept more lines into the macro.
For example on previously attached file I pasted one page with data into DATA tab (which has 49 lines). Now I need to be sure that if I paste 2 or 3 pages of the data at the same time, one after another it would still work and work out the pages needed.

Another thing is the issue that comes up in summary tab. As per attached file you can see that when macro is executed in cell D16 onwards it will change formatting from 'General' to 'Number' and instead of page numbers only separated by commas. Although I try to change it, after macro is executed it changes formatiing to how it was before.

Finally... I have worked out that apart from Adobe having issue with length of the characters in one line, it can also only print 24 labels per one shot.
Could you please set up another condition that would only allow there to be 24 numbers per line? I still need to keep 70 characters per line though.

Thank you in advance for your help with this. I have tried to work it out on my own, however I am afraid my knowledge about these things is still too limited to be able to build it on my own... I keep on learning though!!!

Sorry - what I meant is that I can only print 24 labels at one go. This is why I need there to be only 24 numbers but not going over 70 characters as well.

Thanks again.

Could we embedd pages numbers in DATA sheet so it would always show up even if I paste data on it? That would be in cell L49, L98 and so on (as in attached sheet).

Hi there,

Macro works fine until cell C18 Summary tab where number of characters in cell exceeds 70 and all lines below will have wrong formatting and return values like

1.46147147148148E+17,149,149,150,150,150,151,151,152,153,154,155,156,156,157,158,158,159,159,160,161,162,163,164

Originally Posted by sintek

I was referring to this ...

All of these page numbers are there however in white colour so it wouldn't be visible on the print out.

Originally Posted by sintek

What file are you making use of for testing...Thus far all your uploads have been different?

None of your uploads go as high as 164???

To make sure it works if more stores are added to it I have added some more in DATA tab starting from row 58 onwards to 76 to see if it will work correctly.

The file is the same one but with more stores added to it

Hi there,

I have tested the file today and it has been working great, there were no issues observed for now and hoping this will stay this way

There are some idea and plans to improve it but I think the focus now is to make sure it works fine without any problems and then I may ask for some more help.

Amazing piece of work, many thanks for your help on that, it is greatly appreciated!