Excel-auto increment in groups of three while printing

[fishbin]

My boss just handed me a project that I think is possible with Excel, but may be better handled with Access or any one of a hundred other billing software programs.

On a worksheet (in the print area) I need to make a data overlay for three identical invoices. I’d do this by tweaking the row and column spacing and cell alignment. This is so when our preprinted invoice forms are printed, our current information is inserted into the blank placeholders.

Below these three invoice templates (and out of the print area) will be rows, each with unique companyname/address and account number data.

We need Excel to take the first row of data and insert it into to proper placeholders (cells) on the top invoice template.

Then the next template would receive data from the next row and the third template would receive data from the third row. I can do all of the above myself with my limited knowledge of Excel.

The problem is that we have close to 500 of these to print each month, so we need some way to pour the corresponding data into each of the three invoice templates while printing and then auto-increment/insert for printing data rows 4,5,6 then 7,8,9 etc.

I’m very familiar with Access but not Excel but I have no choice in the matter, it has to be in Excel.

Any help would be appreciated. Go easy on me though, as I have very little experience with VBA.

fish

 

[Zathras]

Method 1:

1. Re-structure your database so that the data from the three lines all appear on the same line.

2. Then use built-in mail merge techniques available between Word and Excel.


If that is not possible, then...

Method 2:

1. Keep the data on one sheet.

2. Use a second sheet to create your pro-forma invoice forms.

3. Designate a cell to contain the row number for the "current" row being printed.

4. Use the =INDEX() function to populate the variables in your invoice forms, pointing to the data sheet by row (from the designated cell) and column (hard-coded). Use row+1 and row+2 to access data from the other, related rows.

5. Set your print area.

6. Initialize the designated cell for the first set of three rows.

7. Record a macro to print the first invoice.

8. Modify the macro to wrap the printing code with a loop that increments the designated cell by 3 with each iteration, stopping when it reaches a blank cell in the data area.

9. Run the macro and go get a cup of coffee.

10. When you become proficient in macro writing, you can put steps 5 thru 8 all in the same macro (including the code to get the data in the first place). But to get started it is sufficient to do some of the preparatory work outside of the macro, just to keep things simple.