VBA to insert page total (print area) in footer with cell-like formatting

Hi there,

New to the forum, I am relatively familiar with excel but have only entry level understanding of VBA and am having trouble achieving a solution to a problem I have encountered at work. Over the weekend I attempted to develop solutions to several issues that we have with regard to a standard schedule which is used when going out to tender for a specific type of construction. The spreadsheet requires:

1. Numbering for each line item prefixed by the section of construction its sheet is based on, skipping headings (denoted by bold text) and blanks. OK
2. Automated section headings and project name / client name in header / top of each sheet. OK
3. Conditional formatting which covers borders, alignment, and bold / standard text. I can do this myself.
4. A footer which contains 3 sections which update dynamically as information is inserted / removed. This is what I need help on

The footer specifically requires:

1. A section containing the 'Project Number'.
2. A section which contains information in this format: section number/page number (ex. section 2, page 7 would show "2/7")
3. A section which contains the sum of the right most column for that page (a page total) and a description beside it which would read either 'to collection', or 'to summary' depending on whether the footer was located on the last page ('to summary') of the sheet or elsewhere ('to collection'). Ideally i would like to be able to link this number to the section summary page (ex. Total for page 7 = $21,000) and for it to dynamically update.
4. Formatting of footer to match the excel spreadsheet attached to this post.

I have put some effort into searching for a solution myself and the closest match was this thread on another forum (http://forum.chandoo.org/threads/how...-footer.12144/) however I could not get the VBA code to work on my version of Excel (2010).

Thank you in advance to anyone who finds time to help me out, I appreciate that I am asking for a lot and not really offering much (*anything... ).

Regards,

Luke

Originally Posted by tony h

I will have a look tomorrow. Having problems with the download at the moment.

Hi Tony,

Thanks for this, I will continue to look into the issue myself when I have spare time. If you need me to reupload the file or host it somewhere else let me know.

Cheers,

L

I have moved this thread to the correct area of excel forums, the new one can be found here: http://www.excelforum.com/showthread...76#post4573676