macro in copied workbook fails

[icystorm]

Here are the conditions:

I have a printing macro in a workbook that works perfectly. When I
create a copy of the workbook (along with the macro) the macro in the
newly copied workbook no longer functions. Both workbooks reside in
the same directory. Any ideas about what may be causing this behavior?

For clarity, here is the macro (Tom Ogilvy assisted me with this
earlier in the week):

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

 

[Chip Pearson]

What do you mean "no longer functions"? Do you get an error
message? What happens when you run the code?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[icystorm]

Thanks for reponding, Chip.

When I run the copied macro (in the copied workbook), I receive a run
error (code 13) for this part of the macro:


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

The entire section above is highlight in yellow during debugging.

Oddly, the identical macro functions perfectly in the original
workbook.

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

Thanks for any solutions you may offer.

Joseph

 

[icystorm]

For more amplification:

A runtime error '13' type mismatch is occuring in my copied workbook.

This portion of the code fails:

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

The filename is:

Schedule Manager - Apr 19, 2006 - 1806z.xls

Could the filename be the problem? I've read about filenames causing
runtime errors before. Any thoughts?


Thanks,
Joseph

 

[icystorm]

Okay, I believe I tracked down my own problem.

This explains the "Run-time error:'13' Type Mismatch" error message
when you click a macro or a function on a menu in Excel 2002 (and I
assume it applies to Excel 2000, SP3 as well).

http://support.microsoft.com/default.aspx?scid=kb;en-us;821292

The issue I am experiencing is in Excel 2000, SP3 on a corporate PC.
My employer will not upgrade at this time. Ergo, is it possible to
write the following macro in a different way, to avoid the Run-time
error:'13' Type Mismatch when it's original workbook is copied to a new
workbook?

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

Thanks for any assistance or insight you can offer.

Joseph