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.
Dim LR As Long, X As Long
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")
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
Sheets("test").Range(MyPasteRange(j)).PasteSpecial xlPasteValuesAndNumberFormats 'xlPasteValues
j = j + 1
Range("A1") = "No Data Found"
Function CheckFileIsOpen(chkfile As String) As Boolean
On Error Resume Next
CheckFileIsOpen = (Workbooks(chkfile).Name = chkfile)
On Error GoTo 0
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
I guess I am not being able to direct my code to focus on test.csv. Can someone please help me with this?