Results 1 to 5 of 5

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

  1. 03-21-2013, 06:00 PM #1
    antonio
    antonio is offline
    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
    Reply With Quote Reply With Quote
  2. 03-21-2013, 06:24 PM #2
    ashu1990
    ashu1990 is offline
    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
    Reply With Quote Reply With Quote
  3. 03-21-2013, 07:15 PM #3
    Excel Fox
    Excel Fox is offline
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    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
    Reply With Quote Reply With Quote
  4. 03-22-2013, 01:49 AM #4
    antonio
    antonio is offline
    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
    Reply With Quote Reply With Quote
  5. 03-26-2013, 01:53 AM #5
    antonio
    antonio is offline
    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.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Error Handling When Range Is Not Defined Properly
    By bobkap in forum Excel Help
    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

Bookmarks

Posting Permissions

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

Forum Rules