Excel VBA creates Subscript error with a for loop

Question

I'm new to VBA but I'm trying.

I'm trying to when you press a button on the worksheet, to open another Excel file, get the rows from it and copy it to my current Excel file. I want this automated so you only have to press 1 button.

Private Sub CommandButton1_Click()
    Dim src As Workbook
    Set src = Workbooks.Open("C:\Users\gregg\Downloads\download.xls", True, True)

    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("download").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count

    Dim iCnt As Integer
    For iCnt = 1 To iTotalRows
        Worksheets("Elszamolas").Range("B" & iCnt).Formula = src.Worksheets("download").Range("B" & iCnt).Formula
    Next iCnt
End Sub

This is what I have currently. I want to copy from file download sheet download to file napiElszamolas sheet Elszamolas. When I run the script I get "Subscript out of range" "Run-time error 9".

If the error Is in the loop, I would put the line into two. Just to see if it's the Elszamolas or the download sheet that cause the error. — the_lotus
Which line gives the error? The message you're getting points to one of your sheet names being wrong. — Tim Williams
@the_lotus the error is in the loop because if I only take out the loop it doesn't give error — Márton Gergő
@TimWilliams There is just a pop up window "Run-time error '9': Subscript out of range — Márton Gergő
Look here. There you find all the typical cases for this situation. — Storax

urdearboy urdearboy · Accepted Answer · 2018-12-05T18:34:17

You could have an issue with unqualified ranges which has been corrected for in the below code. Please give this a try! If this does not work, my guess would be that you have a minor mismatch between your sheet name written in the code and the sheet name that actually exists in the workbook.

Code also updated to dim worksheet variables for readability, use more standard last row (LR) calculation and loop name (i).

Option Explicit

Private Sub CommandButton1_Click()

Dim src As Workbook: Set src = Workbooks.Open("C:\Users\gregg\Downloads\download.xls", True, True)
Dim DL As Worksheet: Set DL = src.Sheets("download")
Dim EZ As Worksheet: Set EZ = src.Sheets("Elszamolas")

Dim i As Long

For i = 1 To DL.Range("B" & DL.Rows.Count).End(xlUp).Row
    EZ.Range("B" & i).Formula = DL.Range("B" & i).Formula
Next i

End Sub

Excel VBA creates Subscript error with a for loop

1 Answers