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

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

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