Repeating Rows at the bottom of each printed sheet

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi Fergus, ideally you would like a repeat rows option but for the footer rather than at the top but unfortunately theres not an option for this. The following example code will place values of cells A55,A56 and A57 on a separate line in the footer.

Code:
Sub Macro1()
Dim StrFtr As String
StrFtr = Range("A55") & vbLf & Range("A56") & vbLf & Range("A57")
ActiveSheet.PageSetup.LeftFooter = StrFtr
End Sub

hth
 
Upvote 0
I'm looking for the same solution to repeating row at the bottom.

Does the solution you give able to accomodate more than 255 characters as well as graphics?


thanks
 
Upvote 0
Hi Joel, I dont know the limitations of the footer. I would say you definitely wouldnt be able to fit 255 char on one line so you would need this to be split into several lines. Are you saying 255 char is in one cell or thats the combined text for several cells?

All that this is doing is grabbing the values from the cells and placing it in the left hand footer, just as if you had manually typed it in. The VbLF bit of the code just means a line feed (pressing enter to go down another line) to separate the cell values. You could just manually copy and paste a value from a cell into the footer if you wanted.

The other option I suppose is to have dummy sheets that repeats the last few rows on each page which is a bit of mucking around.

hth
 
Upvote 0
Hi Parry, thanks for your suggestion, but where do I type in that code? I tried typing it into the footer but of course all that did was reprint the code in the left footer. As a further question, can the range be extended to cover a number of cells eg Range ("A55:G55") or even refer to another worksheet eg Range (Sheet5!"A55:G55").
 
Upvote 0
Hi Fergus, no code goes somewhere else. Will the range A55:G55 fit on one line do you think? If so heres an amendment for you with a space in between each of the cell values. Im not sure how this will look as it wont line up with other rows so it may be better to do this another way.

To place the code into your workbook take these steps.

1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)

To run the macro select Tools|Macros|Macro and select MyFooter then click the run button. To see the result click on print preview or page setup.

Code:
Sub MyFooter()
Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range
Set Sh = Worksheets("Sheet5")
Set Rng = Sh.Range("A55:G55")

For Each c In Rng
StrFtr = StrFtr & c & " "
Next c

ActiveSheet.PageSetup.LeftFooter = StrFtr
End Sub
 
Upvote 0
hi guys... and thanks

actually, there are a few rows i would like to repeat at the bottom. Total of characters are more than 255. and there is a graphic to repeat as well.

i tried modifying the repeating row at the top (VBA) so that i can repeat those rows at the bottom but so far unlucky...

these rows are "notes" rows that my boss insists on having it at the bottom of the page.... right now actually i just pre-printed the bottom notes and print the top data when required. i'm having headaches on how to do this as more often than not, my boss change the wordings for the notes.

my print volume is around 100-200 pages at a time.

any help is really appreciated.

thanks
 
Upvote 0
Hi Parry, Thanks for your last offering, with that as a start and a lot more research into vb I was able to get a half way reasonable repetition of lines on each page, but as the macro was still effectively only writing into the standard tatty excel footer I was still constrained by the limitations of the footer (255 characters and all that). For information of anyone else reading this I was eventually able to achieve what I wanted by creating the "footer" in CorelDraw, exporting it as a .jpg file and then pasting the resultant picture into the left footer in excel - works a treat. The initial creation in CorelDraw took some experimentation to get the positions of things right, but once done it's good for all time. editing in future is easy, just reopen the CorelDraw file, update and re-export as the same .jpg file and re-insert in the excel footer.
 
Upvote 0
Hi guys,

Insert jpg as a footer?

I do most of development on Excel/Office 97 although I have Excel/office 2000 on the other PC.

This may be my own blunder but I have not notice a way to insert an image as a footer in both of my Office version. Hopefully, you could enlighten me. Perhaps I missed something?

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top