Excel Bottom of sheet Printing 1

[Statesman]

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

 

[zelgar]

Make a custom footer to include the items you wish to have. In the Header/Footer tab on the Page Setup, hit the Custom Footer button to create your signature blocks.

Zelgar

 

[joerd]

So are you going to sign off every page ?
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.

 

[handleman]

With Office 2k3 there is the ability to include a picture in the footer. I'm not sure what the limitations are on size, but if your title block isn't too big you could insert it as a picture.

 

[Ashereng]

A picture would only work if the title block is static. If the title block is dynamic (e.g Pg. 1/400, Pg. 2/400), then not so good.

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?

 

[Statesman]

Some good points made guys.
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.

 

[rb1957]

you could do it the old way ... have your title block pre-printed on paper, and reduce your paper size for 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.

 

[IRstuff]

There are some limitations, to be sure, but you can create a full-page title block image, inserted in the header and then the date and page inserts in the footer and the texting will print over the inserted image.

TTFN

 

[Ashereng]

Statesman,

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?

 

[sdz]

I have the same problem and found the best way was to make up a title block similar to our standard calc sheets and put it at the top. Excel easily allows rows to be repeated at the top of each page.

 

[dimjim]

Couldn't you just insert the footing every 30
cells or whatever to make it do the same thing?

 

[Ashereng]

dimjim,

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?

 

[rb1957]

our version of excel has that option (repeating rows as a header or footer greyed out (unavailable) ... i'll ask IT why?, tho' i expect the usual answer "it was a product improvement"

 

[cummings54]

My process engineering department uses a foxpro database to hold the line information. We can print out to an excel workbook. If the client requires excel base datasheets. But that isn't much help.

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

 

[joerd]

rb1957,

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.

 

[rb1957]

thx hoerd i was accessing the page set-up under the print menu, and the option i want is under file/page set-up as you point out ...

thx bill, MS triumphs again

 

[rb1957]

sorry joerd (can't type, can't read!)

 

[joerd]

Never mind, glad to be of help.
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.