Hi,
The following macro functions correctly in an Excel 2003 workbook. However, when I create a copy of the workbook, the macro generates a run-time error 13, "Type Mismatch" when it is run from the copied 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
-----------------------
Please note, I am using another macro to create the copy of the original workbook that contains the functioning macro.
The filenames are:
Schedule Manager (MASTER).xls (the original file with the functioning macro)
and
Schedule Manager - Apr 21, 2006 - 1756z.xls (an example of the copied workbook with the copied macro that generates the run-time error)
I have been trying to solve this issue for a couple of weeks with no success. Any ideas or suggestions would be very appreciated at this point.
Best Regards,
Joseph
Run-time error 13, "Type Mismatch" in Excel application
ThankYouDriveThrough
Hi Derek,
Thanks kindly for commenting.
'Schedule 1' is a worksheet in the workbook, so "'Schedule 1'!Print_Area" refers to the print area of that specific worksheet.
The area that pops during debugging is this:
ActiveWorkbook.Names.Add Name:= _
"'Schedule 1'!Print_Area", _
RefersTo:="=" & Worksheets("Admin").Range("F95").Value
The print area is dynamic, based on the contents of a cell F95 in worksheet 'Admin', and a static print range won't produce the results I need.
I think the error is occuring because I am copying the workbook to a new workbook with a new name. Something adverse apparently transpires in some copied macros when a macro copies a workbook with the original, functioning macros. Result The macros still function perfectly in the original workbook, but when some are run from the copied workbook, a type mismatch errors occurs.
Best Regards,
Joseph
Robert Dunlop
Hi Joseph,
Your doing a save as, would have thought that would have been ok. If you had been using the file system objects then I'd have recommend changing it.
If you comment out the line thats causing the type mismatch error does the copy work
Dean Massey
Hello Joseph,
Not going to pretend I have an answer but I tried you code.
Your named range "'Schedule 1'!Print_Area" doesn't seem to be a valid name for a range. If you define the named range yourself (Insert->Name->Define on the menu) you can't add it. That wouldn't throw a type mismatch error though.
Looks like your defining a print area by creating a named range. Try this code instead...
ActiveSheet.PageSetup.PrintArea = "$B$7:$F$17"
Change the range to what you need to print out. That wouldn't cause a type mismatch error either though.
What line of your code is the error happening on
Ripon12
It's actually saved via another macro, Derek...
Sub saveandexit()
'
' saveandquit Macro
'
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With ThisWorkbook
.SaveAs Filename:="\\Arghou\data\OPS\schedule\Schedule Manager - " & Format(Now(), "mmm dd, yyyy - hhmm") & "z.xls"
Application.Quit
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
---------------------
Best Regards,
Joseph
Spock101
Hi Joseph,
Are you making a copy of the workbook doing a save as (or save copy as) or are you copying the file through file system objects