Excel Header/Footer Question

[erikhertzel]

All,

I have an excel spreadsheet that I am using (Excel 2000) that is a simple spreadsheet that tracks a schedule (Months are in the columns with tasks in the rows.

I have header and footer information that when you look at the print preview it looks great. But, I wanted to have some place to type below the footer information, so I moved the footer up in the margin about 4" and then made my spreadsheet data to end there so the footer would show up right below it.

I wanted to use the remainder of the page to type in, so I merged the remaining rows and did a word wrap, but when I type in it, it totally messes up the page layout, it moved the footer up into spreadsheet (in preview it shows the remaining rows even though they are blank. Even if I don't merge the cells and just type in a single cell in the remainder of the page below my spreadsheet data, it does the same thing.

How do I fix this issue so I can have a footer above some typed data on the page?

Thanks,

Erik

 

[xlhelp]

Footer IS the end of the printed page.

I would be easy to put your info in the footer after putting in a couple of Line feeds.

Stupidity has no handicap

 

[erikhertzel]

I see what you are saying. I would like to use the spreadsheet and not the footer to type my info. in because it could always be changing. How could I incorporate this?

Thanks,

Erik

 

[erikhertzel]

The user seems to think he needs to have the footer above the info that he wants to put in the page. Is this possible?

Thanks,

Erik

 

[kim00]

erikhertzel - If you go into the page setup and click on the margin tab, you can change the placement of the footer well above the bottom of the page by changing that value. Then enter the variable data at the bottom of the page and the footer will be above it.

...kim

 

[xlhelp]

What you are asking not possible without a macro. The macro takes the info from a cell and still puts the info in the footer.

Macro courtsey Tom Ogilvy

Private Sub Workbook_BeforePrint(Cancel As Boolean)
for each sh in Activewindow.SelectedSheets
if sh.Name = "Sheet1" then
sh.PageSetup.LeftFooter = Worksheets("Sheet1") _
.Range("B9").Text
Exit for
end if
Next

End Sub

The macro above puts contents of cell B9 into the left footer.


Stupidity has no handicap

 

[erikhertzel]

kim

That is what I did, placed the footer at like 4" but when I type below it it puts gray rows back in (even though they are not part of the spreadsheet (the rest of the spreadsheet is in outline format.

Erik

 

[erikhertzel]

I put this macro in, but it's not working, I just created a new macro and then pasted it in and went back to Excel and put a value in but it did not put it in the footer, it is still in B9.

What I am doing wrong?

Erik

 

[xlhelp]

The code would needs to go in the
ThisWorkbook module.


On your keyboard press (Alt+F11)


in the project window you will see your workbook listed.
Under it look for an entry for

ThisWorkbook

double click on that


At the top of the resulting module
In the left dropdown, select Workbook
In the right dropdown select BeforePrint

then paste the macro

You are not expecting the contents of B9 to disappear, are you?

The macro is invoked when you print.




Stupidity has no handicap

 

[erikhertzel]

That works except, yes, I do not want it in B9 for example, I want it in the footer. It's showing up both places now.

Plus, it got rid of my stuff that was in my left footer, it deleted it. How can I get around this? Is it possible?

Thanks,

Erik

 

[xlhelp]

Sorry, I don't 'unnerstand'. If B9 is cleared then you would have repopulate it the next time you need to print the WKS. Then what's the point of the macro? You might as well do it manually.

Anyway, here is the modified version

Private Sub Workbook_BeforePrint(Cancel As Boolean)

For Each sh In ActiveWindow.SelectedSheets
If sh.Name = "Sheet1" Then

FooterVal = ActiveWorkbook.Sheets(1).PageSetup.LeftFooter
sh.PageSetup.LeftFooter = FooterVal & Chr(13) & Worksheets("Sheet1").Range("B9").Text
Range("B9").ClearContents
Exit For
End If
Next

End Sub


Stupidity has no handicap