printing macro problem

[icystorm]

The following macro is supposed to define and print a range listed in
cell F95 on a worksheet designated as "admin". Sometimes, the macro
prints the defined range, but other times, the macro prints the entire
worksheet (over ten pages). I cannot figure out why the bahavior is
sporadic. Any thoughts or solutions would be greatly appreciated.

Here's the macro:

Sub print_schedule()
'
' print_schedule Macro
'

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Sheets("Schedule 1").Select
ActiveWorkbook.Names.Add Name:= _
"'Schedule 1'!Print_Area", _
RefersTo:="=INDIRECT(admin!F95)"
Application.ActivePrinter = "\\Print01\ARPRN41 on Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Personnel").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

------------

Incidentally, "admin!F95" contains the following:

=CONCATENATE("'Schedule 1'!$A$1:",D95)

and...

D95 contains a variable: (e.g. $AP$30)

So, the text string in F95 is currently: 'Schedule 1'!$A$1:$AP$30

Thanks kindly.

Joseph

 

[Guest]

I don't see any role for Indirect here:

Sub print_schedule()
'
' print_schedule Macro
'

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Sheets("Schedule 1").Select
ActiveWorkbook.Names.Add Name:= _
"'Schedule 1'!Print_Area", _
RefersTo:="=" & worksheets("Admin").Range("F95").Value
Application.ActivePrinter = "\\Print01\ARPRN41 on Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Personnel").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[icystorm]

Thanks much, Tom. I haven't tested it, but I will tonight at my
office.

I'm not sure I understand why Indirect isn't used to ensure the
concatenated text string is treated as a reference to a cell. I will
try to read more about that function and cell referencing this evening.

As always, thanks again for your assistance. I always learn a lot here
from you and others.

Joseph

 

[icystorm]

Tom,

Your solution worked flawlessly. However, when I copied the workbook
(along with the macro) to another workbook in the same directory, I am
receiving an error (code 13) for this part of the macro:

ActiveWorkbook.Names.Add Name:= _
"'Schedule 1'!Print_Area", _
RefersTo:="=" & worksheets("Admin").Range("F95").Value

For example:

The macro in this workbook works ------> c:\test\test.excel
The copied macro in this workbook fails ------> c:\test\copy of
test.excel

Any thoughts or suggestions?

Thanks!

Joseph

 

[icystorm]

Edit:

For example:

The macro in this workbook works ------> c:\test\test.excel
The copied macro in this workbook fails ------> c:\test\copy of
test.excel

Sorry, that should say:

For example:

The macro in this workbook works ------> c:\test\test.xls
The copied macro in this workbook fails ------> c:\test\copy of
test.xls