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

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
--
HTH...

Jim Thomlinson


"Rock_on_Richard" wrote:

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

Sorry should be...

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

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Rock_on_Richard" wrote:

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,
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

"Jim Thomlinson" wrote:

Sorry should be...

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

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Rock_on_Richard" wrote:

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

Change this line:

with Sheets("Sheet1")

to match the name of the worksheet.

with Sheets("YourSheetNameGoesHere")

Rock_on_Richard wrote:

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

"Jim Thomlinson" wrote:

Sorry should be...

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

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Rock_on_Richard" wrote:

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

--

Dave Peterson

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" wrote:

Change this line:

with Sheets("Sheet1")

to match the name of the worksheet.

with Sheets("YourSheetNameGoesHere")

Rock_on_Richard wrote:

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

"Jim Thomlinson" wrote:

Sorry should be...

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

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Rock_on_Richard" wrote:

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

--

Dave Peterson