XL - Ranges from multiple sheets into 1 listbox (or better method?)

[davedave24]

Hi. I'm writing a procedure that puts data into multiple worksheets, based on the listcount of a listbox. Each sheet has space for 20 lines; any more and a new sheet will be generated, and the additional lines filled there, and so on.

Later I will need to be able to 'load' one of these workbooks back into my userform and repopulate the controls with the data from the sheet (because they may be saved as a draft, and more added to later on)

How do I get the data from multiple ranges (always A16:F25) on different sheets (the number of which we won't know - generally no more than 2-3), into 1 multicolumn listbox? Is this even possible?

I'm thinking of a simpler way to do it - when the data is written to each sheet, I could have a blank sheet that just contains each record in 1 huge block, and load *that* into the listbox. The user never need see that sheet. If this method is used, is it possible that when the user prints the workbook from the userform, that this sheet not be printed?

 

[SkipVought]

hi,

When I see questions of this type, ie '(similar) data on multiple sheets', a [highlight #F57900]RED FLAG[/highlight] semaphores in my brain. This type design is fraught with sorrow, tears and unnecessary contortions and effort.

Each sheet has space for 20 lines

Why this abitrary limit?

But wait! There will be more probing questions, I am sure.

You would be best served to have ALL your lists containing similar category of data to be in ONE LIST. If it is at all possible, focus your efforts to consolidate your data, from which your question evaporates.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[davedave24]

Yeah I've basically been asked to fit the data into a sort of invoice sheet that will be printed out. Since each sheet, the way they've set it up, has only 20 rows of data, they make a new sheet and add the rest to that. PITA. I've been working out the flow of it on several pieces of paper but what I think I'm going to do is just have the customer data (date/invoice number etc.) at the top, then paste all the data from the listbox regardless of how much there is, then just write the totals etc. at the bottom of that, wherever it may be.

The main reason is so that it prints out neatly but it's more effort that it's worth. How then could I ensure it inserts pagebreaks in the appropriate places, or could this be setup automatically in the excel template?

 

[SkipVought]

Have you tried inserting a page break? You can do that anywhere you like and remove them.

But why not consider this: Decouple the data management of the invoice and the production of the invoice as a print product. ALL invoices ought to reside in a SINGLE TABLE, with some sort of status to indicate the final print date. From such a table, ANY invoice of ANY status could be viewed or analyzed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]