Run Time error '9': Subscript out of range

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:
Attachment 642

Code:

---

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

---

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

Code:

---

wbk = activeworkbook.name
windows(wbk).activate

---

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

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

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.

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