[SOLVED] Produce a report in specific layout from varying data

**Maff2002** · 09-18-2009, 03:36 PM

Hi,

My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work.
Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.
I have attached a simplified example to try and explain what i would like to happen.

Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.

I want to produce a management report from this data, with a specific layout.
I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.

I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there.
I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.

Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.

It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.

I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!

Thanks,
Matt

**broro183** · 09-18-2009, 06:19 PM

hi Matt,

It sounds like you're willing to learn by experience so here's some pseudo code which may help you with the logic of some coding:

Please Login or Register  to view this content.

I got a bit carried away, but I'm off to bed now so there should still be some challenges for you to solve (esp if you're new to macros

). I'll check back at some stage this weekend & may dig up some code for the rest of the automation. To get you started though, the best option for guided discovery is to record a macro as you save the file manually & then convert the "save as" name in the code to a variable.

Goodluck :-)

hth
Rob

**Maff2002** · 09-20-2009, 01:51 AM

Thanks for this, I should have time to play around with it this afternoon.

I enjoy learning this stuff, it's quite amazing how useful excel can be in many different workplaces and situations, it seems a shame that the majority of people don't use it to its full potential.

**Maff2002** · 09-20-2009, 06:16 AM

well, I have had an interesting few hours on this, thanks.

I added a button to run the code. The code does a good job counting the rows in sheet 1 and then writing the correct number of report headings in sheet 2, however I haven't managed to get the rows data to write properly yet.

Thanks,

I'v attached an updated copy of the file, and here is the code so far:

Please Login or Register  to view this content.

**broro183** · 09-20-2009, 11:01 AM

hi Matt,

I've thought about it a little more this time, made some changes to my variable names, added page break setting & have tested the code this time - it will take away the fun for you, but you can find a new challenge now ;-)

hth
Rob

**Maff2002** · 09-20-2009, 01:56 PM

Thanks Rob, that works very nicely and is exactly what I was looking to acheive.

I haven't looked at the code yet, but I will definately be picking it apart to learn how it all works. I'm glad you put in the bold titles and the line formatting, I was just searching how to do that!

Also, I have been playing with the macro record like you suggested, I'v learnt a couple things from that already, like how I could get the workbook to save automatically.

Thanks!
Matt

**broro183** · 09-20-2009, 04:56 PM

Thanks for the feedback, I'm pleased, that I could help &, to see someone else who enjoys learning & using the potential of Excel.

Thanks for marking the post as solved, feel free to add to my reputation by clicking on the blue scales (it's appreciated

).

Thanks
Good luck with your next challenge
Rob