I've been looking for a way, for about 6 months off and on now, to have Excel print data from the first and last row that is printed, minus repeated rows at the top. I'm currently working with Excel 2007/Win 8 at home, though this could be used in my workplace at well which would mean typically either Excel 2007/WinXP OR Excel 2010/Win7, depending on when they update the machinces, etc.
Specifically, my spreadsheet has patchlists for telecom wiring. There are 7 rows which are repeated at the top of every page, and then a long list of wires within each bundle. I'm looking for Excel to print, at the footer of every page, "Pairs x to y" where x and y would be the numbers from Column A of the first and last rows (again, disregarding the repeated rows) of each page. So, for example, page one would show "Pairs 1 to 25", two would show "Pairs 26 to 50" and so on. The goal is that the printed format of this would be landscape and placed into a 3-ring binder, so someone who needed to review the sheets could simply scan the bottom of the pages until they found the specific cable and pair they were looking for, similar to how a dictionary has the first and last words printed at the top of the page.
I would prefer to not have macros do this, as too many users see the macro warning and flip out, or completely ignore it altogether, but that doesn't seem very likely based on what I've seen regarding having formulas or cell references in headers/footers. So macro/vba away if that's the case. I'm fairly good with VBA (not a master, though), I just can't find how it would retrieve the cells to be printed on the sheet.
As a last resort I currently have the pages set to print 25 pairs per page, so as long as this doesn't change for one reason or another (printing margins overridden by the printer, or whatnot) I could probably get away with a formula in the footer that would pull the page number and calculate the rows that should be on it, such as "Pairs [(page#*25)-24] to [(page#*25)]" but haven't found a way to do this either, and would be the much less desirable way to do this in case, as I mentioned, the number of rows printing is changed for whatever reason.
Any help would be greatly appreciated. Thanks!
Specifically, my spreadsheet has patchlists for telecom wiring. There are 7 rows which are repeated at the top of every page, and then a long list of wires within each bundle. I'm looking for Excel to print, at the footer of every page, "Pairs x to y" where x and y would be the numbers from Column A of the first and last rows (again, disregarding the repeated rows) of each page. So, for example, page one would show "Pairs 1 to 25", two would show "Pairs 26 to 50" and so on. The goal is that the printed format of this would be landscape and placed into a 3-ring binder, so someone who needed to review the sheets could simply scan the bottom of the pages until they found the specific cable and pair they were looking for, similar to how a dictionary has the first and last words printed at the top of the page.
I would prefer to not have macros do this, as too many users see the macro warning and flip out, or completely ignore it altogether, but that doesn't seem very likely based on what I've seen regarding having formulas or cell references in headers/footers. So macro/vba away if that's the case. I'm fairly good with VBA (not a master, though), I just can't find how it would retrieve the cells to be printed on the sheet.
As a last resort I currently have the pages set to print 25 pairs per page, so as long as this doesn't change for one reason or another (printing margins overridden by the printer, or whatnot) I could probably get away with a formula in the footer that would pull the page number and calculate the rows that should be on it, such as "Pairs [(page#*25)-24] to [(page#*25)]" but haven't found a way to do this either, and would be the much less desirable way to do this in case, as I mentioned, the number of rows printing is changed for whatever reason.
Any help would be greatly appreciated. Thanks!
