Hi Ian,
Quote:
Originally Posted by iansan5653
Could you give me a more detailed explanation? I have never done macros before.
|
Basically, a macro would have to be coded to look at each row to see whether it has a value in your 7th column. If so (let's assume row 1), it would then look at the next row (row 2) to see if that has a value in your 6th column and, if so, cut columns 2-6 from that row and paste them into, say, columns 8-12 on the current row (1). The code would then look at the next row down (3) to see if that has a value in your 6th column. If so, columns 1-7 from row 1 would have to be copied to row 2 and columns 2-6 from row 3 cut & pasted to columns 8-12 on row 2. Row 2 would then become the 'current' row and the process would repeat until another row is found with a value in your 7th column. At that point the whole process would start over. After all this processing to get the data onto a single row per client & category is done, any remaining rows would be deleted.
As you can see, this is quite an involved process.
If you're going to drive the letter generation from Excel, the macro would also need to sort the data by client, start a Word session, export the data for the first client to it, save a copy, then process the next client untill all are done.