Dynamic header row when printing a report

QuestionEdit

Thank you very much for your assistance. Theworkbook that I have recently developed includes a 'Report' feature, that allows the user to select a desired subset of the information, which is then copied to another worksheet and a printable report is generated. As part of this, I have VBA code that automatically accounts for the variable overall length of this report. Also, starting on the second page of the report, a column header is placed at the top of each page.

Each report consists of a listing of 5 sets of items: 'A', 'B', 'C', 'D', and 'E'. Each set has a variable number of items. In other words, the report consists of 5 tables, each of varying length, that are listed in succession. One report may have 50+ items listed under set 'A', while another report may have only 5 items listed under set 'A'. Likewise, the other sets have a variable number of items. I would like to have the header at the top of each page be tied to the corresponding table, and would like to have the headers include all 3 rows that are actually at the head of each of the 5 tables. The motivation for this is that, for example, when someone is looking at the list on page 17, they would only need to look at the top of the page to know which set (A, B, C, D or E) they are looking at, instead of looking through the previous pages to find the beginning of the current table. However, I am only able so far to implement one header within the entire report. The one row header I now have is just a listing of the column names. If I was able to repeat all three table header rows, the table name and table description would also be listed at the top of each page.

Any leads would be greatly appreciated.

AnswerEdit

Based on what information you have provided I would think the easiest solution would be to put the 5 tables on separate worksheets. Each worksheet could have its own unique column headers. In addition, you could use the table name as the name of the worksheet tab, and put the &Tab keyword in the worksheet header so that when the worksheets print they will automatically print with the name of the table at the top. This would also have the advantage that in the report every table would start at the top of a new page without having to add manual page breaks.

Of course your report would now consist of 5 worksheets, so you would have to modify your VBA code to print out all five sheets. I assume you code is currently using the PrintOut method to print the worksheet of interest, something like this:

 Worksheets("Sheet1").PrintOut ...

In order to now print all worksheets in the workbook in one statement you can use:

 Worksheets.PrintOut ...

Feel free to follow up if this is not an acceptable solution for you.