Populate invoice from database sheet (Attached)

I have tried for two weeks before finally joining the forum out of desperation; can someone here please help me?

I am trying to populate an invoice from a list of items in another sheet

I have two sheets in one workbook, Sheet1 (Invoice) and sheet2 (Database).
Both sheets have 3 columns, B, C & K with headings (quantity-Description-cost).
Sheet 1 is a list of 2000 items; each item can be selected by entering a quantity.

I would like to use a macro that copies the items selected in sheet2 to the first blank row (counting from the top down B14:K2000) to sheet 1.
Only the selected items would be copied and placed sequentially in sheet1, no blank cells or rows would be copied.

I have attached a workbook with two macros, everything works just fine if you hit the 'copy rows' macro but it deletes the formulas in the total column, so I would prefer to use the 'copy cells' macro but the items are being copied with blank rows and it will only populate one page where the 'copy rows macro' populates more than one page with header and footer.

Code below if it helps.

Thanks for looking, Mayweed.

Cell macro

Please Login or Register  to view this content.

Row macro that adds totals and formats if the page is full

Please Login or Register  to view this content.

I'm not really clear what you are doing.

I can see the idea of transferring selected items, but why bother with the formulas? Place the formulas in the Invoice and only copy the Qty, Description & item cost

You are right, the copy cell macro is doing that but it copies the blanks as well, how can that be fixed?. I put the copy row together so that if the items exceed the page size of 35 it places a new total on the last page.
I suppose that the copy row macro works just fine but it kills the invoice for manual population after it's been run. The copy cell macro would only populate one page.

This code will transfer selected items to the Invoice, all formulas need entering into the invoice. The coe will not delete them

Please Login or Register  to view this content.

Just for chuckles, this shows you how you can get the same thing in realtime with no VBA. I've used the empty column A on each sheet to create an index. As you add QTY values in the database, the hidden index changes and the rows indexed will appear instantly on the Invoice sheet.

I've colored the font in column A white to keep the indexes invisible. Change color temporarily if you want to see the indexes in action.

I would add sufficient empty rows to the form, then add code to hide empty rows. I've extended it to Row 1021, just amend it to suit yourself

Please Login or Register  to view this content.

Shall I mark this Solved?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Every post has one in the upper right corner.