Header index

If it is true that:

1. You are using an Excel spreadsheet for your AddressBook, and
2. You have it set up to use column headers in row 1 such as:
   Name Address Phone

Then the following code should get you started:

Please Login or Register  to view this content.

The code above assumes that Col-A is Names, and that you have set the print options such that the header row prints on every page.

You're welcome. I changed the line:

iRightHeaderRow = RowsPerPage * ThisPageNum + 2

to:

iRightHeaderRow = RowsPerPage * ThisPageNum + 1

Also, I realized that the last page would probably not be a full sheet, so I will look at that tomorrow.
Did the calculated number of pages agree with the number of pages reported in "Print Preview"? Was the calculated number of rows the same as printed?

Thanks for your reply.
There was no print preview, it just went ahead and printed without asking.... Ideally I would like to be able to print odd/even pages. And where can I get information about - it it visual basic i need to know to write and trouble shoot this code?
I tried the change you suggested and the results were only slightly different.
Print preview did not appear, it went straight to the printer.
When you say "the calculated number of rows" (or pages), I am not sure where that information is shown.

Here is the modified code. I tested it with a 1206 row sheet and it worked on all 37 sheets, including the short last page; however, you must setup your pages to repeat row one:

PHP Code: 

Sub pageformatter()
     Dim TotalPages As Long
     Dim ThisPageNum As Integer, TotalRows As Single
     Dim RowsPerPage As Single
     
     'get number of pages on the WorkSheet
     TotalPages = ExecuteExcel4Macro("Get.Document(50)")
     
     'get the number of ROWS on the WorkSheet
     TotalRows = ActiveSheet.UsedRange.Rows.Count
     RowsPerPage = Int(TotalRows / TotalPages)
     
     Range("A2").Select
     For ThisPageNum = 1 To TotalPages - 1
          With Worksheets(1)
               .PageSetup.LeftHeader = .Range("A" & ActiveCell.Row).Value
               .PageSetup.RightHeader = .Range("A" & ActiveCell.Offset(RowsPerPage, 0).Row).Value
          End With
          ActiveWindow.SelectedSheets.PrintOut From:=ThisPageNum, To:=ThisPageNum, Copies:=1
          ActiveCell.Offset(RowsPerPage + 1, 0).Select
     Next ThisPageNum
     'Print the last page
     With Worksheets(1)
          .PageSetup.LeftHeader = .Range("A" & ActiveCell.Row).Value
          .PageSetup.RightHeader = .Range("A" & TotalRows).Value
     End With
     ActiveWindow.SelectedSheets.PrintOut From:=TotalPages, To:=TotalPages, Copies:=1
End Sub 


-----------------
notes:
-----------------
1. Print Preview is found on the "FILE" menubar; when you select "print preview" the status bar at the bottom of the window will say "preview: page x of y".
2. When you paste in the code in the VB editor, if you right-click on the variables and select "add watch" from the pop-up menu, the watch window will open and add the variable.
3. You use F8 in the VB editor to step through the code. You will see the changing values of any variables that you have added to the watch list.
4. As for books, I only have two:
- VBA Developer's Handbook by Ken Getz and Mike Gilbert, 1997 ( $50)
- Definitive Guide to Excel VBA, by Michael Kofler, 2003 ($55)
* The (free) advice/help I get on this forum beats the books!

From Excel 95 Help:
Excel 4 was the macro language used prior to the changeover to Visual Basic. There are a few functions available in that language that are not available, or require extra coding to obtain, in VB so Excel includes these macros "internally", at least up to XL2000, which I use.
Get.Document() is one of the internal macros accessible via ExecuteExcel4Macro()
#50 causes it to return the number of pages that will be printed, #88 will return the name of the active workbook.