how do I print a named dynamic range

[Rock_on_Richard]

I am not sure if this question should go to this group, the worksheet
functions group, or the general questions group. Please redirect the
question if I have chosen the wrong group.

I want to print a named dynamic range so that the print area changes as the
dynamic range changes. The named dynamic range is easy enough to set up
using the count function nested within the offset function in the name
definition. When I used the range name in the print area field on the print
setup page the correct area is calculated and printed the first time a print
command is issued but the range name is replaced with the calculated range
values and the range is frozen, not dynamic after that first printing. How
do prevent this from happening so that each time I print it goes out and
calculates a new range.

I am using Office 2003 SP2

Thanks

 

[Jim Thomlinson]

Add the following code to the ThisWorkbook module in your project (Right
click the Excel icon next to the word file in the menu bar and select View
Code)...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet1").PageSetup.PrintArea = Sheet1.Range("DynaRange").Address
End Sub

 

[Jim Thomlinson]

Sorry should be...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
with Sheets("Sheet1")
.PageSetup.PrintArea = .Range("DynaRange").Address
end with
End Sub

 

[Rock_on_Richard]

Jim,
Thanks for the suggestion. I entered ran your code and got a runtime error
'9': subscript out of range message. Do you have any further suggestions
please?
Thanks

 

[Dave Peterson]

Change this line:

with Sheets("Sheet1")

to match the name of the worksheet.

with Sheets("YourSheetNameGoesHere")

 

[Rock_on_Richard]

Thanks for the wonderful help. I'm getting closer to a solution.

This works great until I close and reopen the file. Then it acts like the
code is not there at all. To get it to work again I have to delete all of
the code, save and close the file, close Excel, them reopen the file and
reenter the code. What do you think is going on and how do I fix this little
problem.

Thanks again,

 

[Dave Peterson]

If you just close excel and then restart excel and reopen your workbook, does it
work ok?

If it does, it sounds like that you have some other code that is turning off
excel's event handler.

Next time it happens, try this:

Hit alt-f11 (to get to the VBE)
hit ctrl-g (to see the immediate window)
type this and hit enter.
application.enableevents = true

Then back to excel and hit file|Print Preview.

If it works, you're gonna have to find the offending code that toggled that
setting off and didn't toggle it back on. It could be in any open workbook!

 

[Rock_on_Richard]

Once Excel is closed then reopened the code doesn't work. I discovered my
security default was set too high and the file was opening with macros
disabled. When security setting was lowered and I was able to open the file
with macros enabled everything works just great.

Thanks Dave and Jim for all of your help.