Run time Error, Subscript out of range

Question

I am trying to run a macro to generate a data and it was working initially however, now it is giving an error, "subscipt out of range". Please I need assistance:

 Sub paste()
'
' paste Macro
'

Application.ScreenUpdating = False
Application.Calculation = xlManual
MyPath = ThisWorkbook.Path
Sheets("Raw_Data_Agent").Visible = True
    Sheets("Raw_Data_Agent").Select
    Columns("B:P").Select
    Selection.ClearContents
    Workbooks.Open Filename:=MyPath & "\Raw_Data_Agent.xls"
    Columns("A:O").Select
    Selection.Copy
    Workbooks("Real Time Agent AHT And Login Tracker").Activate
    Sheets("Raw_Data_Agent").Select
    Range("B1").Select
    ActiveSheet.paste
    Sheets("Raw_Data_Agent").Visible = False
    Workbooks("Raw_Data_Agent").Activate
Application.DisplayAlerts = False
    ActiveWorkbook.Close False
    Sheets("AM And Process Wise").Select
    Calculate
    ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True

End Sub

Workbooks("Real_Time_Agent_AHT_And_Login_Tracker").Activate, the line breaks here — user3275237, Feb 10 '14 at 13:56

score 0 · Answer 1 · edited May 23 '17 at 12:05

I would place my bet on one of the sheet names not being accurate. OR the focus moving to the wrong workbook. And hence you should avoid the use of .Select/.Activate INTERESTING READ

Your code can be re-written as (UNTESTED)

Sub paste()
    Dim thisWb As Workbook, thatWb As Workbook, AnotherWb as Workbook
    Dim thisWs As Worksheet
    Dim nCalc

    On Error GoTo Whoa

    With Application
        .ScreenUpdating = False
        nCalc = .Calculation
        .Calculation = xlManual
    End With

    Set thisWb = ThisWorkbook
    Set thisWs = thisWb.Sheets("Raw_Data_Agent")
    Set AnotherWb  = Workbooks("Real Time Agent AHT And Login Tracker")

    MyPath = thisWb.Path

    With thisWs
        .Visible = True
        .Columns("B:P").ClearContents

        Set thatWb = Workbooks.Open(Filename:=MyPath & "\Raw_Data_Agent.xls")

        thatWb.Columns("A:O").Copy AnotherWb.Sheets("Raw_Data_Agent").Range("B1")

        .Visible = False

        Application.DisplayAlerts = False
        thatWb.Close False
    End With

    thisWb.Sheets("AM And Process Wise").Calculate
    thisWb.RefreshAll

LetsContinue:
    With Application
        .ScreenUpdating = True
        .Calculation = nCalc
        .DisplayAlerts = True
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Also when you are working with events, always use error handling else if you get an error, those events are not restored to defaults.

Thanks for the comment, however, when I try to run the code line by line, this is where the issue is exactly: Workbooks("Real_Time_Agent_AHT_And_Login_Tracker").Activate — user3275237, Feb 10 '14 at 13:54
I just amended the code and if you get an error on the line `Set AnotherWb = Workbooks("Real Time Agent AHT And Login Tracker")` then that means it cannot find the workbook with that name. — Siddharth Rout, Feb 10 '14 at 14:01

Run time Error, Subscript out of range

1 Answers1