printing macro problem

I

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
 
T

Tom Ogilvy

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
 
I

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top