Runtime 9 error Check.
Hi
I have a survey spreadsheet with the following macro. For some reason when a colleague runs it he gets a runtime 9 error. I have looked through some other posts and i have seen a lot of comments saying change the "Windows" line to start with "Workbooks" Would this solve the problem in this scenario too, or would there be another reason the error is flagging too? Many Thanks Matt Sub Post_To_Job() ' ' ' Re-Coded 17-09-09 by Matt ' Application.ScreenUpdating = False Application.DisplayAlerts = False Range("B5").Copy Workbooks.Open Filename:="W:\Trade_Sales_Planning\Matt\MZ Response Boxv2", Password:="A123456=b" Sheets("Job").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).FormulaR1C1 = "=NOW()" ActiveCell.Offset(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(0, -1).Select ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Code added here 'Name added to column C ActiveCell.Offset(0, 2).Value = Environ("UserName") ActiveWorkbook.Save ActiveWorkbook.Close Windows("MoanZone.xls").Activate Range("B5").Select Application.CutCopyMode = False Selection.ClearContents ActiveWorkbook.Save Application.DisplayAlerts = True Application.ScreenUpdating = True UserForm1.Show End Sub -- Matt Lynn Message posted via http://www.officekb.com |
Runtime 9 error Check.
I'm assuming that the line that's failing is:
Windows("MoanZone.xls").Activate If the user doesn't have a workbook open named Moanzone.xls, then this line will fail. If the user has a workbook that's open, but has two windows into that workbook open (Window|new window would be how it's created), then this line will fail--since the names of the windows would be: moanzone.xls:1 and moanzone.xls:2 I'll do my best to avoid the windows collection and try to use the workbooks collection. This will not fix the error if the workbook isn't open, though. "Mattlynn via OfficeKB.com" wrote: Hi I have a survey spreadsheet with the following macro. For some reason when a colleague runs it he gets a runtime 9 error. I have looked through some other posts and i have seen a lot of comments saying change the "Windows" line to start with "Workbooks" Would this solve the problem in this scenario too, or would there be another reason the error is flagging too? Many Thanks Matt Sub Post_To_Job() ' ' ' Re-Coded 17-09-09 by Matt ' Application.ScreenUpdating = False Application.DisplayAlerts = False Range("B5").Copy Workbooks.Open Filename:="W:\Trade_Sales_Planning\Matt\MZ Response Boxv2", Password:="A123456=b" Sheets("Job").Select Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).FormulaR1C1 = "=NOW()" ActiveCell.Offset(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(0, -1).Select ActiveCell.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Code added here 'Name added to column C ActiveCell.Offset(0, 2).Value = Environ("UserName") ActiveWorkbook.Save ActiveWorkbook.Close Windows("MoanZone.xls").Activate Range("B5").Select Application.CutCopyMode = False Selection.ClearContents ActiveWorkbook.Save Application.DisplayAlerts = True Application.ScreenUpdating = True UserForm1.Show End Sub -- Matt Lynn Message posted via http://www.officekb.com -- Dave Peterson |
Runtime 9 error Check.
Dave - Brilliant
Many Thanks Matt Dave Peterson wrote: I'm assuming that the line that's failing is: Windows("MoanZone.xls").Activate If the user doesn't have a workbook open named Moanzone.xls, then this line will fail. If the user has a workbook that's open, but has two windows into that workbook open (Window|new window would be how it's created), then this line will fail--since the names of the windows would be: moanzone.xls:1 and moanzone.xls:2 I'll do my best to avoid the windows collection and try to use the workbooks collection. This will not fix the error if the workbook isn't open, though. Hi I have a survey spreadsheet with the following macro. [quoted text clipped - 51 lines] Message posted via http://www.officekb.com -- Matt Lynn Message posted via http://www.officekb.com |
All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com