CODEWITHSUNDEEP
excelvba
Anshu
Anshu

Reputation: 51

Excel Macro- Run-time error '9': Subscript out of range (within For Loop to copy)

This is a simple macro code to copy range A2 until last row from one workbook and paste the same in another workbook. I am a newbie and googled many pages but failed to help self. The code is pasted below-

`

Sub TC_Creation_Sample()
Dim aPath As String, aFile As String, bFile As String
Dim FinalRow As Integer, x As Integer

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

aPath = "C:\temp\"
aFile = aPath & "Config"
bFile = aPath & "TC_Template"
Workbooks.Open (aFile)
Sheets("Config").Activate
'FinalRow = Cells(Rows.Count, "A").End(xlUp).Row
FinalRow = Worksheets("Config").Range("A2").End(xlDown)

For x = 2 To FinalRow
            Worksheets("Config").Range("A" & x).Select
            Selection.Copy
            Workbooks.Open (bFile)
            Worksheets("TestCases").Range("A" & x).Select
            ActiveSheet.Paste
Next x       
End Sub

`

Result- Upon debugging, it's found that the code is successfully copy pasting A2 value. However, it's throwing run-time error 9: Subscript out of range. And the error occurs when Worksheets("Config").Range("A" & x).Select is running for the second time after Next x. I am not sure what's really causing this error. Kindly, advise.

Upvotes: 3

Views: 2919

Answers (3)

AdzzzUK
AdzzzUK

Reputation: 298

A number of points to mention.

First - An Excel workbook can contain many millions of rows - but your code is being limited to only 32,768 rows, because you are defining both FinalRow and X as Integers. Use Long Integers instead (Dim X as Long, FinalRow as Long), in case one of the workbooks you open has more than 32,000 rows.

Now onto the cause of the error. When you run the code for the second time through the For-Next Loop, the system hangs, because as far as it's concerned, the active workbook is bFile, and because Config tab doesn't exist on bFile (it's on aFile), it crashes with the unhelpful error message you get (because it can't find the right tab, because it's looking in the wrong place).

Incidentally, your code will attempt to open (bFile) EVERY time the For..Next loop triggers. Do you need to open the file every time? Surely it's just a case of copying the details from one sheet to the next?

You should also have some error handling. If either aFile doesn't contain Config tab, or bFile doesn't contain TestCases tab, the code will crash out. I would strongly recommend that you some error handling in to handles these instances.

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

Is this any better? I assigned your workbooks to workbook variables and took out select and copy methods. I also added .Row as others have also suggested

Sub TC_Creation_Sample()
    Dim aPath As String, aFile As String, bFile As String
    Dim FinalRow As Long, x As Long
    Dim wbA As Workbook, wbB As Workbook

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    aPath = "C:\temp\"
    aFile = aPath & "Config"
    bFile = aPath & "TC_Template"

    Set wbA = Workbook.Open(aFile)
    Set wbB = Workbooks.Open(bFile)

    FinalRow = wbA.Worksheets("Config").Range("A2").End(xlDown).Row
    'FinalRow = wbA.Worksheets("Config").Cells(Rows.Count, "A").End(xlUp).Row

    For x = 2 To FinalRow
        wbB.Worksheets("TestCases").Range("A" & x).Value = wbA.Worksheets("Config").Range("A" & x)
    Next x
End Sub

Upvotes: 1

Limak
Limak

Reputation: 1521

You haven't assigned row number to FinalRow variable. Try:

FinalRow = Worksheets("Config").Range("A2").End(xlDown).Row

Upvotes: 1

Related Questions