Inserting a Range into Footer

If someone believes this is not possible,
that information would also be helpful.

Hi Ornithopter,

Your approach is correct and should work, but you cannot assign the text of a range of multiple cells to a single text string--you must do it one cell at a time, like this:

.LeftFooter = [B237].Text & [C237].Text & ... & [M239].Text

This basically concatenates all the cell text to produce the desired string.

This may not completely produce the desired result, though, if you are expecting a carriage return and linefeed between the text contained in different rows of cells. In order to add line feeds you must concatenate vbLF (or perhaps vbCrLf) into the string where you want the new line to occur.

Hmmm....

Thanks Damon.

It is unfortunate that there is no way to maintian
the formatting of the cells as they have borders
and pictures that I would like to include at the
bottom of every page.

I guess that it would be best to import the
printable area into Word and do it from there?

Another Solution I just thought of (to stay in excel)
would be to have 2 sheets:
one with data, the other with the footer.

Then write a macro that:
1) Calculates how many rows fit on
the current page settings.

2) Calculates how many rows the footer
range requires.

3) Removes all 'Old' footers (if I ran the
macro before)

4) Inserts a copy of the footer at regular
calculated intervals so that it appears
at the bottom of every page and gives
the effect I want.

Thoughts?

One last attempt before I do it the hard way...

BUMP.

Hi again Ornithopter,

Yes, if I understand you correctly the method you outline should work. The problem with using standard headers/footers is that they only accept text, not graphics--and what I believe you want is to paste a picture of the block of cells into the footer. Definitely this could be done with Word, which does support graphics in headers and footers. But of course, as you describe, you can also "simulate" headers and footers by simply placing the text and graphics in the appropriate places in your spreadsheet, and sizing the real header and footer to essentially zero height to make room. This is probably easier than using the Word approach.

My first thought about how to do this would be to create a block of rows on the second worksheet that contains the desired footer (or header). Then your code would do a copy and paste to put those rows into the first worksheet at the desired row locations. Rather than counting rows to determine the paste location, a better method would be to look for a row that contains the PageBreak (see the PageBreak property in the VBA helps) so that you will insert the header/footer in the correct place regardless of the height formatting of the rows. If you want the footer to appear at exactly the same position on each page (height-wise) you will need to do a bit of arithmetic to determine what to set the height of the first (top) pasted row to.

Thanks Damon!

The PageBreak property is an interesting concept...
Will look into it.

This same topic generated some interesting ideas on
in this thread:
http://www.vbaexpress.com/forum/showthread.php?t=426

A sample solution to this problem is posted in the above formum.

Cheers!