Run Time error '9': Subscript out of range

[antonio]

Hello Everyone,

Would appreciate your help on this.

Did the below code for a co-worker to automate some manual functions from his end.

Code works in my laptop but when I'm giving him the file...He is running into a "Run Time Error '9'.

I checked everything on his PC & also changed the necessary 'Open Filenamepath', but the error still appears. How can I get this fixed?

Thanks for your guidance.
Antonio

Error image:
642


Sub GenerateBook()
On Error GoTo OpenWorkBook:
Dim BookName As String
BookName = "Pro-Active Tracing"
Workbooks("Pro-Active Tracing").Activate
GenerateReport2
Exit Sub

OpenWorkBook:
If Err.Number = 9 Then
Workbooks.Open Filename:="C:\Users\sguidone\Desktop\Commodity Performance\Pro-Active Tracing.xlsm"
Resume
End If
GenerateReport2
End Sub

Sub GenerateReport2()
Application.ScreenUpdating = False
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("a5:a5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("b5") <> ("") Then
Range("$b5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("b5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("b5:b5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("c5") <> ("") Then
Range("$c5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("c5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("c5:c5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("d5") <> ("") Then
Range("$d5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("d5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("d5:d5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("e5") <> ("") Then
Range("$e5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("e5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("e5:e5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("f5") <> ("") Then
Range("$f5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("f5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("f5:f5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("g5") <> ("") Then
Range("$g5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("g5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("g5:g5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("h5") <> ("") Then
Range("$h5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("h5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("h5:h5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("j5") <> ("") Then
Range("$j5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("j5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("i5:i5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("k5") <> ("") Then
Range("$k5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("k5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("j5:j5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("l5") <> ("") Then
Range("$l5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("l5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("l5:l5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("m5") <> ("") Then
Range("$m5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("m5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("m5:m5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("n5") <> ("") Then
Range("$n5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("n5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("n5:n5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("q5") <> ("") Then
Range("$q5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("q5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("o5:o5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("y5") <> ("") Then
Range("$y5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("y5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("A5").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub GenerateReport3()
Application.ScreenUpdating = False
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("p5:p5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("ag5") <> ("") Then
Range("$ag5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("ag5").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("q5:q5000").Select
Selection.Copy
Workbooks("Pro-Active Tracing").Activate
Sheets("Master").Select
If Range("ap5") <> ("") Then
Range("$ap5000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("ap5").Select
Selection.PasteSpecial Paste:=xlPasteValue
End If
Workbooks("Master Commodity Performance").Activate
Sheets("Master").Select
Range("A5").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[ashu1990]

assign the file name to a string and then activate it.



wbk = activeworkbook.name
windows(wbk).activate

[Excel Fox]

Certain that that doesn't make any difference. Check if

Workbooks("Master Commodity Performance") actually exists (check for spelling mistakes, trailing spaces etc)

[antonio]

Thank you. I tested what you had suggested but no luck.

It works in my laptop but not on my friend's PC. Yes, I do change the path location...

Have attached files, I removed all formulas.

Simply open both files and select "CALLING ALL" button which will move the test data to other sheet.

Let's see if you guys get a run time error.

[antonio]

Hello...can anybody test out the attached file above? I just want to ensure if this is a pc or Excel issue.