VBA: Copy range. run time error 9 - subscript out of range

Question

I am new to VBA. I am trying to copy columns from one workbook to another workbook. Below is the sub I am trying to use but getting an error of "run time error 9 - subscript out of range". Any suggestions?

Sub copydata(wbSource As String, wsSource As String, rangeSource As String, wbDest As String, wsDest As String, rangeDest As String)
    Workbooks(wbSource).Worksheets(wsSource).Range(rangeSource).copy Destination:=Workbooks(wbDest).Worksheets(wsDest).Range(rangeDest)
End Sub

Sub result()
    ' I also tried to set wsSource and wsDest to 1 but still doesn't work
    Call copydata("es.csv", "es", "A:B", "Workbook1.xlsm", "result", "A:B")
End Sub

Thanks

EDIT: They are in the same directory. And I create the module in Workbook1.xlsm

It is not enough that they are 'in the same folder'. They must be in the same workspace in order to reference another workbook (e.g. .csv) from another without using fully qualified external references. — user4039065

exSnake exSnake · Accepted Answer · 2017-08-25T23:28:23

They have to be both open when you copy, that they are both in the same folder doesn't matter.

You should open them, then you can copy data.

Check both sheets name (es for es.csv, result for Workbook1.xslm)

Sub copydata(wbSource As String, wsSource As String, rangeSource As String, 
wbDest As String, wsDest As String, rangeDest As String)
    Workbooks(wbSource).Worksheets(wsSource).Range(rangeSource).Copy 
Destination:=Workbooks(wbDest).Worksheets(wsDest).Range(rangeDest)
End Sub

Sub result()
    ' I also tried to set wsSource and wsDest to 1 but still doesn't work
    If Not IsWorkbookOpen("es.csv") Then OpenWorkbook ("es.csv")
    End If
    Call copydata("es.csv", "es", "A:B", "Workbook1.xlsm", "result", "A:B")
End Sub

Sub OpenWorkbook(fileName As String)
    Dim activePath As String
    activePath = Application.ActiveWorkbook.Path
    Application.Workbooks.Open (activePath & Application.PathSeparator & 
fileName)
End Sub

Private Function IsWorkbookOpen(wbName As String) As Boolean
    Dim wb As Workbook

    On Error GoTo Handler
    Set wb = Workbooks(wbName)
    If wb.Name = wbName Then
        Set wb = Nothing
        IsWorkbookOpen = True
    Else
Handler:
        IsWorkbookOpen = False
    End If
End Function

VBA: Copy range. run time error 9 - subscript out of range

1 Answers