Excel VBA if file closed then open and paste else just paste data

Question

After opening the CSV file, my VBA macros copy data from different tabs in a workbook to it. This element is operating properly. But before I open the CSV file and paste data into it, I want to make sure that it isn't already open. Just paste the data if it is already open.

Sub BU_Macro()



    Dim LR As Long, X As Long
    ThisWorkbook.Activate


    With Sheets("Report Group")

        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        MyCopyRange = Array("A4:A" & LR, "B4:B" & LR, "C4:C" & LR, "D4:D" & LR) 'Put ranges in an array
        MyPasteRange = Array("A1", "B1", "C1", "D1")

        Dim myData As Workbook
        'open target csv file if not already opened
        If CheckFileIsOpen("test.csv") = False Then
            Set myData = Workbooks.Open(strFilePath & "test.csv")

        End If


        Worksheets("test").Select
        Sheets("test").UsedRange.Clear

        If LR > 1 Then
            j = 0
            For X = LBound(MyCopyRange) To UBound(MyCopyRange) 'Loop the array copying and pasting based on element in the array
                .Range(MyCopyRange(j)).Copy
                Sheets("test").Range(MyPasteRange(j)).PasteSpecial xlPasteValuesAndNumberFormats 'xlPasteValues
                j = j + 1
            Next

        Else
            Range("A1") = "No Data Found"
        End If

    End With

End Sub
Function CheckFileIsOpen(chkfile As String) As Boolean

    On Error Resume Next

    CheckFileIsOpen = (Workbooks(chkfile).Name = chkfile)

    On Error GoTo 0

End Function

If the file is closed, it opens it and pastes the date, But if the file is already open, I get an error:

Run-time error '9':
Subscript out of range
on line-
Worksheets("test").Select

I guess I am not being able to direct my code to focus on test.csv. Can someone please help me with this?

narikkadan · Answer 1 · Sep 21, 2022

Slightly re-worked to add full workbook/sheet qualifiers and avoiding activate/select.

Sub BU_Macro()

    Dim LR As Long, X As Long, MyCopyRange, MyPasteRange, strFilePath
    Dim wb, myData As Workbook, shtPaste As Worksheet

    Set wb = ThisWorkbook

     'Put ranges in an array
    MyPasteRange = Array("A1", "B1", "C1", "D1")

    'open target csv file if not already opened
    If CheckFileIsOpen("test.csv") = False Then
        Set myData = Workbooks.Open(strFilePath & "test.csv")
    Else
        Set myData = Workbooks("test.csv")
    End If

    Set shtPaste = myData.Sheets("test")
    shtPaste.UsedRange.Clear

    With wb.Sheets("Report Group")

        LR = .Range("A" & .Rows.Count).End(xlUp).Row

        If LR > 1 Then
            MyCopyRange = Array("A4:A" & LR, "B4:B" & LR, "C4:C" & LR, "D4:D" & LR)
            'Loop the array copying and pasting based on element in the array
            For X = LBound(MyCopyRange) To UBound(MyCopyRange)
                .Range(MyCopyRange(X)).Copy
                shtPaste.Range(MyPasteRange(X)).PasteSpecial _
                           xlPasteValuesAndNumberFormats 'xlPasteValues
            Next
        Else
            shtPaste.Range("A1") = "No Data Found"
        End If

    End With

End Sub