Text in every page footer

[Don Wells]

I've tried your option. The format of the cell M152 is even set to date format. But still the hidden rows does not get unhidden.

• Open the Visual Basic Editor (Alt + F11)

• View the Immediate window (Ctrl + G)

• Type in "Application.EnableEvents = True", without the quotes.

• Key in Enter while the cursor is still on the line you just entered,

• Try the workbook again

If you are still unsuccessful:

• Place a Breakpoint on the "Private Sub Worksheet_Change(ByVal Target As Range)" line in the Sheet12 code.

• Enter a date in M152

• The VBE should open in Step mode. Step through the code and see what happens.

Let me know. I'm standing by.

[adam]

Thanks for the help Don. It works fine now and I'm happy with that.

[adam]

One more doubt if I may ask.

Suppose the data rows in the sheet "NewOrder" are from 19 to 173 excluding the headers which gives a total of six pages.

If I fill rows 19 to 26 and then run the macro the "printOrig" sheet shows only the first two pages, but not the rest. But if I write some data in row 173 the sheet print preview shows all the six pages.

How could I avoid this?

[Don Wells]

One more doubt if I may ask.

    The code is currently designed to adapt to the number of records. The fact that it yields 2 pages instead of one when only several records are used is a bug which can be fixed.
    If you want to print out empty pages; that can be easily arranged, However if you elect this option; future changes to the layout will present problems.
    Please advise of your wishes.

[Don Wells]

One more doubt if I may ask.

Hi Adam
    I have reworked the code to allow the user to decide whether to print the complete table or just the active records. Try it and advise.

[adam]

Thanks for the help Don.

My wish is that when the macro is run; the sheet "NewOrder" will hide the empty rows from range 19:173 and open the sheet "PrintOrig" with only rows containing data in them with the footer. The column that is to be looked for empty row is column "D".

Currently, the footer text appears after the last data row in every page. Couldn't it be made to appear after leaving an empty row so that it would be easy to distinguish between the data rows and page footer texts in every page.

I hope I have made my wish clear.

Thanks in advance for the help.

Note: I do not wish the message to appear each time the macro is run.

[Don Wells]

Thanks for the help Don.
My wish is that when the macro is run; the sheet "NewOrder" will hide the empty rows from range 19:173 ...

    Why do you want to hide the empty rows on the "NewOrder" sheet?    I thought this activity was in support of getting the printed page(s).

[adam]

You are right Don. I too guess that there is no need to hide empty rows from the sheet "NewOrder" as the sheet "PrintOrig" does the job.

With this situation how could it be made possible to make the footer rows appear after a blank row on every page of the sheet "PrintOrig" ?

[Don Wells]

Stand by.

[adam]

Yes Don. Thanks in advance.

[Don Wells]

Here you go

[adam]

Thanks for the help Don. The new version in awesome.

So just to give a final touch how could the sheet "printOrigin" be deleted when the print preview is closed? Since this may mislead the user.

or else how could it be made so that when the macro is run, the sheet gets printed without further print previews

[adam]

In addition to above when the macro is run the code would open the printer dialog and print the sheet "PrintOrig" and then delete the sheet.

[Don Wells]

Thanks for the help Don. The new version in awesome.

So just to give a final touch how could the sheet "printOrigin" be deleted when the print preview is closed? Since this may mislead the user.

or else how could it be made so that when the macro is run, the sheet gets printed without further print previews

    If you wish to conduct further tests:

• Rem out line 550 and

• UnRem line 560

Code: Select all

550         ActiveSheet.PrintOut
'560         ActiveSheet.PrintPreview

    Here's the final product.

[adam]

Thanks for the help Don.

Why does the code take a couple of late seconds to work when the macro is run. I mean even when the screen updating lines are inserted. could this be due to a bug or for some other reason?

[Don Wells]

Thanks for the help Don.

Why does the code take a couple of late seconds to work when the macro is run. I mean even when the screen updating lines are inserted. could this be due to a bug or for some other reason?

    Beats me. On my machine the following times were recorded:

• 0.47 sec with only 1 record and no printing.

• 0.97 secs with the table full of data and no printing.

• 1.64 secs with the table full of data and printing to my inkjet.

    However while conducting the test I found a fault at line 250 which would cause a problem when nearly all rows of the table are to be printed. Change line 250 to the following:

Code: Select all

250         LasRow = Cells(LastTableRow + BotCount + 1, 4).End(xlUp).Row

[adam]

Yeah I did discover the same fault. But now with your line it works fine. Thanks for the awesome help.