VBA Run time error 9: Subscript out of range

1
votes

I am trying to copy the 2nd row from 99 sheets and add them one after the other in the 100th sheet. The vba code is below, however I keep getting the following error:

Run-time error'9': Subscript out of range

Sub copyrow()
    Dim Nrow As Long, Nsheet As Long
    Dim i As Long

    Nrow = 2
    Nsheet = 100

    For i = 1 To Nsheet - 1
        Sheets(i).Cells(Nrow, 1).EntireRow.Copy Sheets(Nsheet).Cells(i, 1)
    Next i
End Sub

The sheet names are Sheet1 (2),... Sheet1 (24).. etc until Sheet1 (99)

I am new to vba and don't know how to fix this error, I suspect it might be in the naming convention but I am not sure.

3
excelvba
Instead of Nsheet put there "Sheet1 (" & Nsheet & ")" - Teamothy
Sheet1 (2) to Sheet1 (99) is 98 sheets. Is the tab name of the last sheet Sheet1(100) (maybe you ought to start with For i = 2 To Nsheet - 1 after adopting Teamothy's suggestion? Please do clarify. - VBasic2008
What do you get if you run this code MsgBox ThisWorkbook.Sheets.Count? - Siddharth Rout
Also your Nrow is not incrementing? If it is not supposed to increment then get rid of that unnecessary variable and change Sheets(i).Cells(Nrow, 1).EntireRow.Copy to Sheets(i).Rows(2).Copy BTW I am not sure what are you trying to copy and what are you trying to achieve? - Siddharth Rout

3 Answers

1
votes

Copy Row from Different Worksheets

Option Explicit

Sub copyRow()

    Const dIndex  As Long = 100
    Const dFirst As Long = 1 ' Easily change the first destination row.
    ' Check this, you said 1, but it's 2 if Sheet (2) is the first worksheet.
    Const sFirst As Long = 2
    Const sRow As Long = 2
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1 (" & 100 & ")")
    Dim n As Long: n = dFirst
    Dim i As Long
    For i = sFirst To dIndex - 1
        wb.Worksheets("Sheet1 (" & i & ")").Rows(sRow).Copy dws.Rows(n)
        ' If you only need values then use this more efficient way.
        'dws.Rows(n).Value = wb.Worksheets("Sheet1 (" & i & ")").Rows(sRow).Value
        n = n + 1
    Next i

End Sub
1
votes

This will copy the specified row from the 99 worksheets to the 100th worksheet, though it might not do it in the expected order.

Sub copyrow()
Dim wb As Workbook
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim Nrow As Long, Nsheet As Long

    ' changed as required e.g. ThisWorkbook or Workbooks("AWworkbook.xlsx")
    Set wb = ActiveWorkbook 
    
    Nrow = 2
    
    Set wsDst = wb.Sheets(100)
    Set rngDst = wsDst.Range("A1")
    
    For Each wsSrc In wb.Sheets
    
        If Not wsSrc Is wsDst Then
            Set rngSrc = wsSrc.Rows(Nrow)
            rngSrc.Copy rngDst
            Set rngDst = rngDst.Offset(1)
        End If
        
    Next wsSrc

End Sub
0
votes

I tried your code and tested for up to 18 worksheets and it works fine. One thing I suggest you to confirm is to make sure object Sheets(100) is referring to your Sheet1(100). You may run in immediate window ?Sheets(100).name to ensure the above. Then maybe you try to narrow down the number of sheets to start testing. Hope you get your solution.