Error 9 - subscript not in range

[WadeExcel]

New VBA User, understand the basics.
Recorded and then edited together some macros into one subroutine.
Have sent that macro out to several people in different locations.
It has worked for everyone except one person in one location.
That person is receiving an error 9 message "subscript not in range".
Have already had this person adjust their macro security settings, as well as uncheck all of the add-ins under tools.
I don't really understand dimensions and upper and lower boundary limits, but I'm guessing from what I've read that's where the problem is with the macro.
Would welcome any help.

The subroutine is:

Sub Patch()
'
' Patch Macro
' Macro recorded 10/23/2009 by Valued Customer
'

'
Windows("2010 Marketing Plan.xls").Activate
Sheets("Hotel Overview").Select
Range("B46").Select
ActiveSheet.Unprotect Password:="passhotel"
Range("B46:I55").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("B46").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Application.CommandBars("Stop Recording").Visible = False
ActiveWorkbook.Save
Windows("Patch.xls").Activate
Windows("2010 Marketing Plan.xls").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Room Revenue Budget").Select
ActiveSheet.Unprotect Password:="passroom"
Range("I122").Select
Selection.Copy
Range("I127").Select
ActiveSheet.Paste
Range("I127").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("I127:I157"), Type:=xlFillDefault
Range("I127:I157").Select
Range("I127").Select
Selection.Copy
Range("L127").Select
ActiveSheet.Paste
Range("O127").Select
ActiveSheet.Paste
Range("R127").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L127").Select
Selection.AutoFill Destination:=Range("L127:L157"), Type:=xlFillDefault
Range("L127:L157").Select
Range("O127").Select
Selection.AutoFill Destination:=Range("O127:O157"), Type:=xlFillDefault
Range("O127:O157").Select
Range("R127").Select
Selection.AutoFill Destination:=Range("R127:R157"), Type:=xlFillDefault
Range("R127:R157").Select
Range("R157").Select
ActiveWindow.SmallScroll Down:=27
Range("I155").Select
Selection.Copy
Range("I162").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("I162:I191"), Type:=xlFillDefault
Range("I162:I191").Select
Range("I162").Select
Selection.Copy
Range("L162").Select
ActiveSheet.Paste
Range("O162").Select
ActiveSheet.Paste
Range("R162").Select
ActiveSheet.Paste
Range("L162").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("L162:L191"), Type:=xlFillDefault
Range("L162:L191").Select
Range("O162").Select
Selection.AutoFill Destination:=Range("O162:O191"), Type:=xlFillDefault
Range("O162:O191").Select
Range("R162").Select
Selection.AutoFill Destination:=Range("R162:R191"), Type:=xlFillDefault
Range("R162:R191").Select
Range("R191").Select
ActiveWindow.SmallScroll Down:=30
Range("I189").Select
Selection.Copy
Range("I196").Select
ActiveSheet.Paste
Range("L196").Select
ActiveSheet.Paste
Range("O196").Select
ActiveSheet.Paste
Range("R196").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I196").Select
Selection.AutoFill Destination:=Range("I196:I226"), Type:=xlFillDefault
Range("I196:I226").Select
Range("L196").Select
Selection.AutoFill Destination:=Range("L196:L226"), Type:=xlFillDefault
Range("L196:L226").Select
Range("O196").Select
Selection.AutoFill Destination:=Range("O196:O226"), Type:=xlFillDefault
Range("O196:O226").Select
Range("R196").Select
Selection.AutoFill Destination:=Range("R196:R226"), Type:=xlFillDefault
Range("R196:R226").Select
Range("R226").Select
ActiveWindow.SmallScroll Down:=27
Range("I224").Select
Selection.Copy
Range("I231").Select
ActiveSheet.Paste
Range("L231").Select
ActiveSheet.Paste
Range("O231").Select
ActiveSheet.Paste
Range("R231").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I231").Select
Selection.AutoFill Destination:=Range("I231:I260"), Type:=xlFillDefault
Range("I231:I260").Select
Range("L231").Select
Selection.AutoFill Destination:=Range("L231:L260"), Type:=xlFillDefault
Range("L231:L260").Select
Range("O231").Select
Selection.AutoFill Destination:=Range("O231:O260"), Type:=xlFillDefault
Range("O231:O260").Select
Range("R231").Select
Selection.AutoFill Destination:=Range("R231:R260"), Type:=xlFillDefault
Range("R231:R260").Select
ActiveWindow.SmallScroll Down:=18
Range("I257").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=15
Range("I265").Select
ActiveSheet.Paste
Range("L265").Select
ActiveSheet.Paste
Range("O265").Select
ActiveSheet.Paste
Range("R265").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I265").Select
Selection.AutoFill Destination:=Range("I265:I295"), Type:=xlFillDefault
Range("I265:I295").Select
Range("L265").Select
Selection.AutoFill Destination:=Range("L265:L295"), Type:=xlFillDefault
Range("L265:L295").Select
Range("O265").Select
Selection.AutoFill Destination:=Range("O265:O295"), Type:=xlFillDefault
Range("O265:O295").Select
Range("R265").Select
Selection.AutoFill Destination:=Range("R265:R295"), Type:=xlFillDefault
Range("R265:R295").Select
Range("I295").Select
ActiveWindow.SmallScroll Down:=21
Range("I293").Select
Selection.Copy
Range("I300").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=12
Selection.AutoFill Destination:=Range("I300:I330"), Type:=xlFillDefault
Range("I300:I330").Select
Range("I294").Select
Selection.Copy
Range("L300").Select
ActiveSheet.Paste
Range("O300").Select
ActiveSheet.Paste
Range("R300").Select
ActiveSheet.Paste
Range("I300").Select
Application.CutCopyMode = False
Range("L300").Select
Selection.AutoFill Destination:=Range("L300:L330"), Type:=xlFillDefault
Range("L300:L330").Select
Range("O300").Select
Selection.AutoFill Destination:=Range("O300:O330"), Type:=xlFillDefault
Range("O300:O330").Select
Range("R300").Select
Selection.AutoFill Destination:=Range("R300:R330"), Type:=xlFillDefault
Range("R300:R330").Select
Range("R330").Select
ActiveWindow.SmallScroll Down:=27
Range("I329").Select
Selection.Copy
Range("I335").Select
ActiveSheet.Paste
Range("L335").Select
ActiveSheet.Paste
Range("O335").Select
ActiveSheet.Paste
Range("R335").Select
ActiveSheet.Paste
Range("I335").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=9
Selection.AutoFill Destination:=Range("I335:I363"), Type:=xlFillDefault
Range("I335:I363").Select
Selection.AutoFill Destination:=Range("I335:I364"), Type:=xlFillDefault
Range("I335:I364").Select
Range("L335").Select
Selection.AutoFill Destination:=Range("L335:L364"), Type:=xlFillDefault
Range("L335:L364").Select
Range("O335").Select
Selection.AutoFill Destination:=Range("O335:O364"), Type:=xlFillDefault
Range("O335:O364").Select
Range("R335").Select
Selection.AutoFill Destination:=Range("R335:R364"), Type:=xlFillDefault
Range("R335:R364").Select
Range("I364").Select
ActiveWindow.SmallScroll Down:=24
Range("I362").Select
Selection.Copy
Range("I369").Select
ActiveSheet.Paste
Range("L369").Select
ActiveSheet.Paste
Range("O369").Select
ActiveSheet.Paste
Range("R369").Select
ActiveSheet.Paste
Range("I369").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=9
Selection.AutoFill Destination:=Range("I369:I399"), Type:=xlFillDefault
Range("I369:I399").Select
Range("L369").Select
Selection.AutoFill Destination:=Range("L369:L399"), Type:=xlFillDefault
Range("L369:L399").Select
Range("O369").Select
Selection.AutoFill Destination:=Range("O369:O399"), Type:=xlFillDefault
Range("O369:O399").Select
Range("R369").Select
Selection.AutoFill Destination:=Range("R369:R399"), Type:=xlFillDefault
Range("R369:R399").Select
ActiveWindow.SmallScroll Down:=27
Range("I397").Select
Selection.Copy
Range("I404").Select
ActiveSheet.Paste
Range("L404").Select
ActiveSheet.Paste
Range("O404").Select
ActiveSheet.Paste
Range("R404").Select
ActiveSheet.Paste
Range("I404").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("I404:I433"), Type:=xlFillDefault
Range("I404:I433").Select
Range("L404").Select
Selection.AutoFill Destination:=Range("L404:L433"), Type:=xlFillDefault
Range("L404:L433").Select
Range("O404").Select
Selection.AutoFill Destination:=Range("O404:O433"), Type:=xlFillDefault
Range("O404:O433").Select
Range("R404").Select
Selection.AutoFill Destination:=Range("R404:R433"), Type:=xlFillDefault
Range("R404:R433").Select
Range("R433").Select
ActiveWindow.SmallScroll Down:=18
Range("I431").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=9
Range("I438").Select
ActiveSheet.Paste
Range("L438").Select
ActiveSheet.Paste
Range("O438").Select
ActiveSheet.Paste
Range("R438").Select
ActiveSheet.Paste
Range("I438").Select
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("I438:I468"), Type:=xlFillDefault
Range("I438:I468").Select
Range("L438").Select
Selection.AutoFill Destination:=Range("L438:L468"), Type:=xlFillDefault
Range("L438:L468").Select
Range("O438").Select
Selection.AutoFill Destination:=Range("O438:O468"), Type:=xlFillDefault
Range("O438:O468").Select
Range("R438").Select
Selection.AutoFill Destination:=Range("R438:R468"), Type:=xlFillDefault
Range("R438:R468").Select
Range("I468").Select
ActiveWindow.SmallScroll Down:=6
ActiveWindow.ScrollRow = 438
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 431
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 420
ActiveWindow.ScrollRow = 415
ActiveWindow.ScrollRow = 408
ActiveWindow.ScrollRow = 400
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 368
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 340
ActiveWindow.ScrollRow = 327
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="passroom"
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Hotel Overview").Select
ActiveSheet.Unprotect
ActiveWindow.SmallScroll Down:=-21
Range("B13:B17").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("G14:G16").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A3").Select
ActiveWorkbook.Save
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True, Password:="passhotel"
Windows("Patch.xls").Activate
'
' Version Macro
' Macro recorded 10/23/2009 by Valued Customer
'

'
Windows("2010 Marketing Plan.xls").Activate
Sheets("Cover").Select
ActiveSheet.Unprotect Password:="passcover"
Range("A16").Select
ActiveCell.FormulaR1C1 = "Version 1.2"
ActiveWorkbook.Save
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="passcover"
Windows("Patch.xls").Activate
ActiveWorkbook.Save
End Sub

 

[Tom Urtis]

Holy cow that's a lot of macro-recorded code. Can you say where the offending code line is within that monster.

Usually, #9 results from a worksheet name, or workbook, or some object that is specifically named in the macro but really does not exist. Or, when a copy and paste is attempted onto a protected worksheet or workbook.

 

[WadeExcel]

Unfortunately, I don't know what line the error occurs. The very first line is: Windows("2010 Marketing Plan.xls").Activate. I made a point to make sure all the users in the field remained their file as noted, knowing that the macro is set up to activate a workbook with that specific name. It worked for all the other users in the field. The macro does use copy and paste functions on protected sheets, but the macro also includes the steps to protect and unprotect the sheets with passwords. Again, it worked for or the other users in the field.

On a different note, the recorded macro includes many rows of:

ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 368
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 350
Etc.

Are all of those rows of information superfluous and I could delete?

 

[Tom Urtis]

In answer to your second question, absolutely yes.

Maybe/Probably, that one person does not have the 2010 Marketing Plan workbook open at the time of macro execution. Hard to say unless we know which code line is erroring.

 

[WadeExcel]

I'm guessing you're right. With that in mind, is there code out there I could copy that would eliminate the need for the workbook to be named specifically? The goal would be to have the macro look for any open workbooks in the same instance of Excel, then have a message box pop up saying "is this the workbook - file you want to update"? If the user says yes, the macro runs, if the user says no, it goes on to look for the next open workbook.

As a user, I've seen this and it made the process simpler. But now as the "Developer" (Is there a term for ultra-beginner developer?) I need to know how to write that code to make things easier for end users.

 

[Tom Urtis]

Well, I would never rely on a user to say that a particular workbook is open. They will make a mistake ans say Yes when really it's No, and say No when really it's Yes.

Instead, verify it yourself.

You said you know the name of the mandated workbook, which is "2010 Marketing Plan.xls". So, programmatically verify it like this:

Function OpenTest(wb) As Boolean
Dim wkb As Workbook
On Error Resume Next
Set wkb = Workbooks(wb)
If Err = 0 Then
Err.Clear
OpenTest = True
Else
OpenTest = False
End If
End Function
 
 
Sub TestOpen()
If OpenTest("2010 Marketing Plan.xls") = False Then
MsgBox "2010 Marketing Plan.xls must be open before this macro can be run.", _
16, "Need to have 2010 Marketing Plan.xls open."
Exit Sub
End If
 
MsgBox "Your code goes here because the 2010 Marketing Plan.xls is open."
 
End Sub

Go ahead and test it to see how it works.

One thing I am suspicious of is, this unsuccessful user might have saved your workbook on their local computer in version 2007 and its extension is not xls but xlsx or xlsm. Just something to ask that person, given the 2010 file name and progressive presence of version 2007.

 

[WadeExcel]

Great ideas about both the way to set up verification as well as checking to see if the user is useing '07. Thank you very much for all of the tips and help! This was a great experience as my first time using this forum.