Repeating Rows at the bottom of each printed sheet

[Fergus]

Although Excel provides an easy way of repeating the same row (eg column headings) at the top of each sheet in a long worksheet does anybody know how I can repeat a row or rows at the bottom of each sheet. The custom footer facility is not flexible enough for what I need. I can either put the rows to be repeated at the end of the worksheet or preferably on a separate worksheet within the same workbook as I have multiple worksheets within the workbook, all of which need to have the same last three or four rows on each page when printed.

 

[Fergus]

Hi Joel, I'm using Windows XP Professional and Office XP Professional so this should work with Office 2000. Go to print preview, then setup, custom footer and there's an icon for inserting a picture. Since writing my previous answer I have refined my footer a bit more. I drafted it in word, using a table the same width as the footer prints out in Excel, which doesn't seem to be controlled by the margins. Run a test footer using say 1's all the way across and measure how wide it is, then set you table in word to be that width and write your custom footer into the table. Save the word file for later editing. Copy and paste the table into CorelDraw, I am still using CorelDraw7, (don't show any borders on the table as Corel doesn't seem to like that). No need to do anything to the file in Corel just save it as a CorelDraw file .cdr. Then use Corel to export the file, (there's an export feature in the file menu). I have tried exporting as .jpg, .bmp & .wmf, of the three I found .wmf was best. Then you can insert the .wmf picture into your excel left footer space, still keeping the other two spaces for say page numbers. Then set your bottom margin on your excel sheet to accomodate your footer. Editing the footer is easy. Open the word file, edit it, save it. Cut and paste into Corel and save and then export as .wmf. On excel, delete the old picture from the footer. Close custom footer, open again and insert revised .wmf file. Having worked this out it is saving me a lot of effort so enjoy.

 

[joelx]

Hi Fergus!

There the difference... you're using office XP and I'm using Office 97/2000.

I just read the help file (part that I missed). It says at the bottom, "You cannot insert graphics or cell references in a header or footer. Use Print Titles if you want to repeat cell contents or a graphics on every printed page."

When I read your earlier comment, I was overjoyed. Now my heart just dropped. I'm back to square one.

Anyway, Fergus, thanks.

Hopefully, other power users of Excel especially 97/2000 users know of a solution and willing to share it. i really appreciate that.

Thanks

 

[Fergus]

Hi Joel, So upgrade to Office XP and save yourself a lot of hassle

 

[joelx]

Hi fergus & all,

upgrade is always the easy way out. but there is no economic advantage in doing that since i have to convince my boss to upgrade all office suite throughout the whole company. this template i'm developing will be used by almost all the staff.

so, i don't think upgarde is the clear option here now.

since i do VBA, I guess i have to do more experiment... perhaps playing with the page break like one of our good friends here suggested.

anyway thanks...

 

[TobyND]

Hello. I changed the code to put onto one line. What I would like to do is add a space between the elements of each cell, but cannot figure out how.

I changed the above code to the following:

Sub Footer()
Dim StrFtr As String
StrFtr = Range("A1") & Range("A2") & Range("B1")
Dim MyString As String
MyString = "A1" & Space(10) & "A2" & Space(10) & "B1"
ActiveSheet.PageSetup.LeftFooter = StrFtr
End Sub

Help on this would be greatly appreciated,

Toby