Results 1 to 5 of 5

Thread: Run Time error '9': Subscript out of range

  1. #1
    Junior Member
    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Question 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:
    error9.jpg

    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
    Last edited by antonio; 03-21-2013 at 06:02 PM. Reason: Attaching image

  2. #2
    Junior Member
    Join Date
    Mar 2013
    Posts
    20
    Rep Power
    0
    assign the file name to a string and then activate it.

    Code:
    wbk = activeworkbook.name
    windows(wbk).activate

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Certain that that doesn't make any difference. Check if

    Workbooks("Master Commodity Performance") actually exists (check for spelling mistakes, trailing spaces etc)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Run Time error '9': Subscript out of range

    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.
    Attached Files Attached Files

  5. #5
    Junior Member
    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Hello...can anybody test out the attached file above? I just want to ensure if this is a pc or Excel issue.

Similar Threads

  1. Replies: 2
    Last Post: 05-14-2013, 01:02 AM
  2. Calculate Time Difference Between Time In HH:MM
    By Stalker in forum Excel Help
    Replies: 8
    Last Post: 03-28-2013, 03:27 PM
  3. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:02 AM
  4. Click Run cycle
    By PcMax in forum Excel Help
    Replies: 6
    Last Post: 11-01-2011, 04:50 AM
  5. Spreading a time range (shift time, etc) in columns.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 08-23-2011, 11:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •