Adding Last-Row Data to a Page Footer

Just one more note: If unsure whether cells to the right of and below the data range are truly empty select these columns and press delete, then do the same with the rows, before running the macro. Even a single cell with just a space character in it (invisible) will affect UsedRange and may spoil the macro. Ctrl+End sends the cursor to the last used cell, but this is not reset automatically when you delete a cell content outside your used range. Depending on a version of Excel it may be reset when you close and re-open the file, but using ActiveSheet.UsedRange in the code does the reset too.

I propose a similar macro which depends on slightly different assumptions, which makes the logic simpler.
1. I assumed the data starts in cell A1 and is in contiguous range. There are no non-empty cells to the right of and below this range. I assume that all this data needs to be printed, hence:
2. The macro sets the print range to UsedRange, i.e., from A1 to the bottom right cell with data. It is important to set the print range explicitly for Excel to calculate number of pages and horizontal page breaks correctly. Also, this seems to minimize (but not 100% eliminate) the subscript-out-of-range error mentioned in the tip. It also helps if Application.PrintCommunication is explicitly set to True.
3. The first row contains Column Headings and will be repeated on all pages.
4. The data should fit on one page horizontally. The user can select landscape orientation and/or larger paper size for the printout to fit more columns of data. The user can also select the scaling in the page setup to fit to 1 page wide and leave the box for tall blank. This may limit the readability though. It is easy to check for page fit by going to Page Break Preview.
5. The macro will work with automatic horizontal page breaks as well as manually inserted.
6. The user can set a smaller print area manually in the spreadsheet, then comment out the line .PrintArea = ActiveSheet.UsedRange.Address, as long as the print area starts in column A (but not necessarily in the first row of data). The column headings will still be printed for the columns selected.
Note: ActiveSheet.HPageBreaks.Item(Pg).Location returns the leftmost cell in the print area just below the horizontal page break #Pg, therefore .Offset(-1,1) returns the cell above the page break in second column.
-------------------------------------------

Public Sub PrintNamesInFooter()
Dim LastRow As Long, Pg As Integer, NumPages As Integer
Dim TopLeft As Range, BotLeft As Range

Application.PrintCommunication = True 'make sure print settings and page breaks are communicated
'between Excel and printer

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.PrintArea = ActiveSheet.UsedRange.Address 'necessary for proper finding of all page breaks
NumPages = .Pages.Count
End With

LastRow = Range("Print_Area").Rows.Count

For Pg = 1 To NumPages
If Pg < NumPages Then
ActiveSheet.PageSetup.RightFooter = ActiveSheet.HPageBreaks.Item(Pg).Location.Offset(-1, 1) 'finds lastname in col. B just before page break
Else
ActiveSheet.PageSetup.RightFooter = Cells(LastRow, 2) 'on last page last Lastname is in last row of data in col. B
End If
ActiveSheet.PrintOut From:=Pg, To:=Pg
Next Pg
ActiveSheet.PageSetup.RightFooter = Empty
End Sub
------------------------------
I have an idea how to get the printout that spans more than a single page horizontally. If someone is interested, please reply to this comment or send me an e-mail.

The macro provided in this tip does the job, however I had hard time following its logic. I figured that the logic was for the macro to print all pages even if not all columns fit horizontally on a single page.

Let’s say the printout takes two pages horizontally and three pages vertically and the page order is set up to “Down then Over”. Footers on pages 1 and 4, 2 and 5, as well as 3 and 6, will be the same, which is correct. However, if the page order is “Over then Down”, most footers will be misplaced, so user must pay attention to the page order setting. The latter setting would have two or more consecutive pages for the same rows, while the former prints first column of pages then goes back to the top row for the consecutive columns.
What the macro is missing, in my opinion, is repeating headings for columns on each page and, if data spans more then one page horizontally, repeating row headings.

I will post another comment with a macro I propose that may fulfill Kristen's wish.