Dynamic Print Ranges

[nunez100]

I am trying to set a dynamic print range with the following formula, but I was hoping that I could apply the same named range to several tabs:

=OFFSET($A$3,0,0,(COUNTA($A$10:$A$260)+14)-COUNTIF($A$10:$A$260,""),5)

What I want to know is whether it is possible to set up a dynamic print area that uses this formula in to 30 tabs of the same layout, named 1-30 (but each with different contents).

The way I was trying to do it was (in office 365), formula --> define names --> then create a new range called 'dynam_print'. I use the offset formula above as the range. Then I refer the print area of that tab back to the named range.

The trouble is that the formula in the named range then gets changed to refer specifically to the tab in question. This then stops me using it as a named range in other tabs. For example, when I apply it to tab 1, the named range above automatically changes to the following formula:

=OFFSET('1'!$A$3,0,0,(COUNTA('1'!$A$10:$A$260)+14)-COUNTIF('1'!$A$10:$A$260,""),5).

I am not sure if I am doing something wrong with this method. I have had it working in the past but I have forgotten exactly how I set it up. Perhaps I would need to just set up 30 different named ranges, one for each tab?

As an alternative method, when I paste the above formula into the 'print range' box in the 'page setup' menu (individually in each tab), the print area does set to the right area, but only at the time of application. It does not move dynamically as new data is added, and when you check the print area in each tab, it has reverted to the actual cell references of the area identified by the named range when it was originally input.

[dflak]

Named ranges, by default, reference the entire workbook. However, they can be changed to be known ONLY to the page on which they were defined.

First go to the name manager and change the scope of the name to the page it was defined on.

Then copy the basic formula to notepad or something similar. Go to each of the 30 pages, establish the same name and paste the formula.

I'm just guessing that this would work.

My second thought on the subject is to make a macro to apply to all pages in the selection at print time -loop through the pages, rest the print range and then print. Too bad there isn't an On-Print event.

[nunez100]

Thank you dflak. I did that and it is working now. In my version of excel (office 365 for mac), there is no way to specify which tabs the named range applies to, so I just made 30 different named ranges, all of which seem to apply to the entire workbook by default in my version of excel.

Just in case anyone else follows up on this, the way it seems to work is that the named ranges can be set in any of the tabs, but (at least in my version of excel), I can only sent the print area for a given tab by being in that tab at the time.

The print area is always called 'print_area', so you cannot customise the name based on the tab it applies to, or else it stops working as a print area.

I set up 30 different named ranges to refer to each of the tabs, and named them 'Dyn_1' to 'Dyn_30', with formula as follows (typed in notepad for pasting easily, as you suggested):

=OFFSET('1'!$A$3,0,0,(COUNTA('1'!$A$10:$A$260)+14)-COUNTIF('1'!$A$10:$A$260,""),5).

=OFFSET('2'!$A$3,0,0,(COUNTA('2'!$A$10:$A$260)+14)-COUNTIF('2'!$A$10:$A$260,""),5).

and so on...

Then I went into the tabs one by one. In tab 1, I found the named range called 'print_area', and change it's source to '=Dyn_1'. I repeated this for all 30 tabs.

I am not sure if there is a quicker way to make the named range generic so that it could be used by all of the tabs at once, but (at least in my excel version), this was the only way that I could get it to work.

Thank you again for your help.

[dflak]

I am not sure if there is a quicker way to make the named range generic so that it could be used by all of the tabs at once, but (at least in my excel version), this was the only way that I could get it to work.

Normally, the nice thing about named ranges is that they are global in scope and you and use them everywhere even in "regular" modules in VB.

However, I found that if you are on a worksheet module, it does not know global names. You have to tell it what sheet you were on when you defined the range.

I think that print ranges work like this. They are sheet-level in scope. I think you did the only thing you could do: slug it out sheet by sheet.

I can foresee where you could have a print macro. that would loop through the selected sheets and set the print range on each of them, and then invokes a "normal" print. This method uses VBA, and it might even take a bit of time (I'm not sure of that). So if you want to avoid VBA then the named range route is the way to go.