Excel - this is a tough one... conditional footers.

I've attached a redacted copy of a macro-enabled file.  One of the macros is event-based: 'Insert row with double-click.'

The problem is that as rows are added, Excel pushes cells G27:M38 down the page, splitting the range of cells and pushing some of it to the next page.  I want that range to ONLY appear on the last page.

Example:  If there are 1-9 line item rows, then there will be room on the first page for the 'TERMS / Signature' cells to be a 'footer' on the last page.  But if the salesperson adds more than 9 line item rows, then the TERMS section rows start getting split up.

What I want is for the TERMS section to be at the bottom of the LAST page ONLY.  So if the sheet has more than 9 'Line Items' on it, the entire TERMS section gets pushed to the bottom of the second page.  And if more line items are added, such that page 2 has more than 9 line items, the TERMS section gets pushed to the bottom of page 3, etc.

It's like a conditional footer - a footer that only appears on the last page.

On a related note, I can't figure out how to even create a footer (not that I need to in this case) on this sheet, since it has 'Freeze Panes' turned on.

Disclaimer - the macros in this workbook were created by others.  I don't know VBA, and am very grateful for the help I've gotten here with this workbook.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Attachment? :-)
Rob HensonFinance AnalystCommented:
Without an attachment, thinking outside the box.

When doing a print, you could have a routine check the number of rows of "Data" before the "Terms" section and if required insert a page break as necessary.

There is a Before_Print worksheet event that can trigger the routine but don't know if it can insert a page break at an approriate time to change the printed document; ie will the "Print File" have already gathered its necessary data which may or not require a page break inserting?

Rob H
eracerAuthor Commented:
I thought I attached it....

Let's try again.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ejgil HedegaardCommented:
Try this.
I have named the range with the terms (name=Terms), and added a Before Print macro in the workbook module.
The macro is setting a manual page break just above the terms, if a page break exist somewhere in the Terms area, due to the number of Item lines.
The page breaks are reset in the 2 other subs, inserting or deleting rows.

It does not do exactly what you have stated in the question (9 lines on each page), because when the terms are moved to the next page, there are space for more lines on the page.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eracerAuthor Commented:
I'm having a tough time with this.  I imported the workbook1 macro, but it doesn't seem to do anything.
Ejgil HedegaardCommented:
The macro must be in the module ThisWorkbook.
It runs before print or print preview, and checks if there is a page break in the Terms area, named Terms.
Then a manual page break is set just above the terms, and the terms will be pushed to page 2.
So nothing happens until print, print preview or save as pdf (equal to print) is used.
Add some extra lines, to have more than 8, and then press print preview.

Observe the page break reset line "Sheet1.Cells.PageBreak = xlPageBreakNone" in the other subs, DeleteMe in Module1 and BeforeDoubleclick in Sheet1 module.
eracerAuthor Commented:
Thanks Ejgil,

I will try that on Monday.
eracerAuthor Commented:
I exported the .bas and .cls files from the Experts-Clean template, then tried to import them into my template.  Nothing happened.  So I opened both templates and copy/pasted the text I found in ThisWorkbook, Sheet1, and Module1.

The macro is keeping the 'Terms section whole as I add rows.  Excellent!  But now when I open my template I get an error message when I run 'Print Preview'  (Run-time error '424': Object Required)  Debug points to:

Set rg = [Terms]

I notice that 'ThisWorkbook changes from ['Workbook' - 'BeforePrint'] to ['General' - Declarations''] when I reopen the saved template.  In the template Ejgil provided, ThisWorkbook shows as ['Workbook' - 'BeforePrint'].  It doesn't save that way when I set it in my template.
Ejgil HedegaardCommented:
The run time error is because the named range Terms is not defined.
You must have missed that in the transfer process.
The named range Terms are the range column G to M, starting with TERMS: in column G, and ending in the row with "Experience Empowering Excellence".
The range is named to keep the reference when the cells are moved when rows are inserted or deleted.
The easiest way to name a range, is to mark (select) the range, then select the box just above column A, left to the formula line, type Terms and Enter.

When you get the error, the macro Workbook_BeforePrint is running.
With the cursor positioned above (outside) the macro, the definition is General-Declaration.
Set the cursor in the code area of the macro Workbook_BeforePrint, and you will see that the definition change to Workbook-BeforePrint.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.