Excel Bottom of sheet Printing
Excel Bottom of sheet Printing
(OP)
Hi,
Looking for some help with printing in Excel. We currently have a database of line conditions (in Excel format)It maybe over 400-500 lines.It is an ideal format for us just now as we are developing the job our process department can add lines to this whenever. However, at the end of the job we really need to issue this as a deliverable line list. My question is quite simple.
How can we print this out as a (standard looking)line list i.e. multiple sheets but with the bottom of each page having a standard title box (complete with check and approved signature boxes) like we have been doing for years. I have tried to page setup where you can get Excel to repeat rows of cells on every page (but unfortunately this only lets you do it at the top of each page)
Is there a way that I can setup a 'range' of standard cells that are always displayed at the bottom of each page ?
I know about 'footer' and this doesnt really do it as I really require a title block at the bottom of each page, not just a date/time/Page No ??
I have searched through various threads to see if this question has come up previously, surely Im not the first person to want to do this ?
Any help, or advice would be gratefully appreciated.
Regards
Statesman
Looking for some help with printing in Excel. We currently have a database of line conditions (in Excel format)It maybe over 400-500 lines.It is an ideal format for us just now as we are developing the job our process department can add lines to this whenever. However, at the end of the job we really need to issue this as a deliverable line list. My question is quite simple.
How can we print this out as a (standard looking)line list i.e. multiple sheets but with the bottom of each page having a standard title box (complete with check and approved signature boxes) like we have been doing for years. I have tried to page setup where you can get Excel to repeat rows of cells on every page (but unfortunately this only lets you do it at the top of each page)
Is there a way that I can setup a 'range' of standard cells that are always displayed at the bottom of each page ?
I know about 'footer' and this doesnt really do it as I really require a title block at the bottom of each page, not just a date/time/Page No ??
I have searched through various threads to see if this question has come up previously, surely Im not the first person to want to do this ?
Any help, or advice would be gratefully appreciated.
Regards
Statesman





RE: Excel Bottom of sheet Printing
Zelgar
RE: Excel Bottom of sheet Printing
Anyway, there is no way you can make Excel repeat rows at the bottom of the page, like it can do at the top, unfortunately. Apparently this is difficult to implement, because it has been a wish for a long time, but will not be possible anytime soon. If you have it, you should try importing the spreadsheet into Access and then design and print a report there.
Alternatively you could
- prepare your printer paper with the title block
- try to paste the whole thing into Word and print from there (likely to be a big mess).
Good luck!
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Bottom of sheet Printing
RE: Excel Bottom of sheet Printing
If you have Access, exporting it to Access and printing it out may be the fastest solution.
Why can't the title block be on top of the page?
"Do not worry about your problems with mathematics, I assure you mine are far greater."
Albert Einstein
Have you read FAQ731-376 to make the best use of Eng-Tips Forums?
RE: Excel Bottom of sheet Printing
I tried the custom footer, but this is very very basic and doesnt really allow the kind of title bock Im after.
The Access route may be one which we will have to investigate if I cant get Excel working (we prefer to keep as much info as possible as most people in our organisation has access to it (and can use it))
As Ashereng stated, the picture option isnt really too good as it is static (plus it would mean having to create a new graphic every time)
Thinking out of the box here, would it be possible to have the spreadsheet rotate about 180 degrees (i.e. turn it upside down) and have the title block at the top (repeatable) ??
(As for why do we need the title block at the top ?!? I think the simplest answer to that one is the quote 'Its always been done that way'
Thanks for some of your advice guys.
RE: Excel Bottom of sheet Printing
you could import the table into Word, and do your word processing in Word ...
sorry, but i have a real problem with people trying to make Excel print reports ... i had a guy (working for me) come up one day (having spent the better part of a year on creating his report in Excel, every page carefully measured out, etc) and say "ok, now how do i print it?" ... damn near hit the fool.
RE: Excel Bottom of sheet Printing
TTFN
RE: Excel Bottom of sheet Printing
I figured as much, but thought I'd ask anyway.
Time for a revolution! Title blocks on top!!!
"Do not worry about your problems with mathematics, I assure you mine are far greater."
Albert Einstein
Have you read FAQ731-376 to make the best use of Eng-Tips Forums?
RE: Excel Bottom of sheet Printing
RE: Excel Bottom of sheet Printing
cells or whatever to make it do the same thing?
RE: Excel Bottom of sheet Printing
Yes you can.
When you add or delete a row of data, you will now have to move up or down all of those footing rows you have inserted.
Your suggestion of course works. It would be nice if there was another solution that didn't require so much maintenance.
"Do not worry about your problems with mathematics, I assure you mine are far greater."
Albert Einstein
Have you read FAQ731-376 to make the best use of Eng-Tips Forums?
RE: Excel Bottom of sheet Printing
RE: Excel Bottom of sheet Printing
I suggest you design an excel template that has your title block and allow for a fixed number of rows (say x rows)to hold line info on the template. A simple VBA routine can be written to add the template to a new book and copy the first x rows from your line list to the tempalte. Then you continue to add templates and copy rows until all lines have been copied to the template.
Assuming you select the lines of interest before starting the macro and the vba modele is located in the line list spreadsheet, the linecode might look something like this:
Sub test()
Const ROWS_PER_SHEET As Long = 30
Const LL_TEMPLATE As String = "C:\Documents and Settings\cummingss\Application Data\Microsoft\Templates\LINELIST.xlt"
Dim lngTotalPages As Long
Dim lngPage As Long
Dim lngRow As Long
Dim rngLineData As Range
Dim rw As Range
Dim wbLineList As Workbook
Set rngLineData = Selection
Set wbLineList = Workbooks.Add(LL_TEMPLATE)
lngTotalPages = rngLineData.Rows.Count \ ROWS_PER_SHEET
For lngPage = 1 To lngTotalPages
For lngRow = 1 To ROWS_PER_SHEET
rngLineData.Rows(lngRow + (lngPage - 1) * ROWS_PER_SHEET).Copy
With wbLineList.Sheets(lngPage)
wbLineList.Sheets(lngPage).Select
.Paste (.Rows(lngRow - (lngPage - 1) * ROWS_PER_SHEET))
End With
Next lngRow
Next lngPage
End Sub
HTH
RE: Excel Bottom of sheet Printing
Could it be that you are accessing the Page Setup from the Print Preview window? Then the repeat heading rows / columns will be unavailable.
You'll need to access File/Page Setup from the normal worksheet view in order to set these parameters.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Bottom of sheet Printing
thx bill, MS triumphs again
RE: Excel Bottom of sheet Printing
RE: Excel Bottom of sheet Printing
You can always count on MS to make the user interface counter-intuitive.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.