×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Bottom of sheet Printing

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

RE: Excel Bottom of sheet Printing

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

RE: Excel Bottom of sheet Printing

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

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.

RE: Excel Bottom of sheet Printing

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?

RE: Excel Bottom of sheet Printing

(OP)
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' clown  )

Thanks for some of your advice guys.

RE: Excel Bottom of sheet Printing

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.

RE: Excel Bottom of sheet Printing

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



RE: Excel Bottom of sheet Printing

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?

RE: Excel Bottom of sheet Printing

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.

RE: Excel Bottom of sheet Printing

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

RE: Excel Bottom of sheet Printing

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?

RE: Excel Bottom of sheet Printing

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"

RE: Excel Bottom of sheet Printing

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

RE: Excel Bottom of sheet Printing

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.

RE: Excel Bottom of sheet Printing

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

RE: Excel Bottom of sheet Printing

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

RE: Excel Bottom of sheet Printing

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

White Paper - The Evolving Landscape of Commercial Battery-Powered Trucks
What’s driving the evolving landscape of truck electrification? What are the barriers, motivators and strategies for accelerating the electric transition? What insights and resources are available for today’s design engineers working to achieve industry disruption and evolution? For answers to these and other pertinent questions, read this white paper. Download Now
eBook - Rethink Your PLM
A lot has changed since the 90s. You don't surf the Web using dial-up anymore, so why are you still using a legacy PLM solution that's blocking your ability to innovate? To develop and launch products today, you need a flexible, cloud-based PLM, not a solution that's stuck in the past. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close