VBA-array problem

If FNum > 0 Then
    For FNum = LBound(MyFiles) To UBound(MyFiles)
       
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
      
                ScreenUpdating = False
    
                Sheets("1").Activate
                RowCount = Range("A1").End(xlDown).Row
                Arr1 = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    
    
                Sheets("2").Activate
                RowCount = Range("A1").End(xlDown).Row
                Arr2 = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
                
                ActiveWorkbook.Close
                
                
Next FNum

I am going to loop some files for data copying. The above code is for copying the data into two arrays, Arr1 and Arr2.

After I run the program, I find out that the data in the second file replaces the first file in the arrays, the third file replaces the second file,etc.....At last, I can only copy the data in the last file.

What should i do??? Thanks

Example

If FNum > 0 Then
    dim arr()
    redim arr(ubound(myfiles),1)
    For FNum = LBound(MyFiles) To UBound(MyFiles)
       
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
      
                ScreenUpdating = False
    
                Sheets("1").Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(fnum,0) = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    
    
                Sheets("2").Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(fnum,1) = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
                
                ActiveWorkbook.Close
                
                
Next FNum

Originally Posted by jindon

Example

If FNum > 0 Then
    dim arr()
    redim arr(ubound(myfiles),1)
    For FNum = LBound(MyFiles) To UBound(MyFiles)
       
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
      
                ScreenUpdating = False
    
                Sheets("1").Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(fnum,0) = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    
    
                Sheets("2").Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(fnum,1) = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
                
                ActiveWorkbook.Close
                
                
Next FNum

If FNum > 0 Then
                Dim arr()
                ReDim arr(UBound(MyFiles), 1)
    
    For FNum = LBound(MyFiles) To UBound(MyFiles)
             
                
                
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
      
                ScreenUpdating = False
    
                Sheets("1").Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(FNum, 0) = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    
    
                Sheets("2").Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(FNum, 1) = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
                
                ActiveWorkbook.Close
                
                
Next FNum
End If
    
    Sheets("1").Activate
    Range(Range("A2"), Range("b2").Offset(UBound(FXArr, 1) - 1)) = arr(FNum, 0)
    
    Sheets("2").Activate
    Range(Range("A2"), Range("d2").Offset(UBound(PositionArr, 1) - 1)) = arr(FNum, 1)
    
    'Sheets("3").Activate
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
End Sub

I try your code, but it shows subscript out of range. Is there anything I miss???
anyway, Thanks for your help!!!

I don't know what you are trying to do, but
1) What is FXArr ?
2) FNum in red is obviously out of range.

    Sheets("1").Activate
    Range(Range("A2"), Range("b2").Offset(UBound(FXArr, 1) - 1)) = arr(FNum, 0)
    
    Sheets("2").Activate
    Range(Range("A2"), Range("d2").Offset(UBound(PositionArr, 1) - 1)) = arr(FNum, 1)

My guess

If FNum > 0 Then
                Dim arr()
                ReDim arr(UBound(MyFiles))
    
    For FNum = LBound(MyFiles) To UBound(MyFiles)
             
                
                
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
      
                ScreenUpdating = False
    
                Sheets(cstr(fnum +1)).Activate
                RowCount = Range("A1").End(xlDown).Row
                arr(FNum) = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    Next FNum
    mybook.Close
End If
for fnum = lbound(arr) to ubound(arr)
    
    Sheets(cstr(fnum + 1)).Activate
    Range(Range("A2"), Range("b2").Offset(UBound(Arr, 1) - 1)) = arr(FNum)
next
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
End Sub

Originally Posted by jindon

I don't know what you are trying to do, but
1) What is FXArr ?
2) FNum in red is obviously out of range.

Sorry i didnt mention my points clearly, but really thanks for your reply!!

I want to copy data form a folder of excel files, then pasting those data into the excel file I assigned. The problem is that every time the program copies data from those files, it will replace the data from the last file. So, at last, it only shows the last file's data. I just want to know, how can I paste all data in the excel file? And what should i amend?

I used this codes for copying and pasting:

If FNum > 0 Then
    For FNum = LBound(MyFiles) To UBound(MyFiles)
            
    Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
    
    Sheets("FX Historical Data").Activate
    RowCount = Range("A1").End(xlDown).Row
    FXArr = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    
    Sheets("Position Data").Activate
    RowCount = Range("A1").End(xlDown).Row
    PositionArr = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
    
    ActiveWorkbook.Close

Next FNum
End If
    
    Sheets("FX Historical Data").Activate
    Range(Range("A2"), Range("b2").Offset(UBound(FXArr, 1) - 1)) = FXArr
    
    Sheets("Position Data").Activate
    Range(Range("A2"), Range("d2").Offset(UBound(PositionArr, 1) - 1)) = PositionArr
   
    
    Sheets("Report").Activate
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

How do you populate MyFiles?

Originally Posted by jindon

How do you populate MyFiles?

This is all of the code

Sub Main_OpenDataFile()
    Dim FileName As String, FilePath As String
    Dim RowCount As Long, LastRow As Long
    Dim FXArr, PositionArr
    Dim FilesInPath As String
    Dim FNum As Long
    Dim MyFiles() As String

        Sheets("FX Historical Data").Activate
            Range(Range("A2"), Range("C100000")).ClearContents
    
        Sheets("Position Data").Activate
            Range(Range("A2"), Range("D100000")).ClearContents
       
    'Fill in the path\folder where the files are
    FilePath = "C:\VBA\4"

    'Add a slash at the end if the user forget it
    If Right(FilePath, 1) <> "\" Then
        FilePath = FilePath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(FilePath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    'Fill the array(myFiles)with the list of Excel files in the folder
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
If FNum > 0 Then
    For FNum = LBound(MyFiles) To UBound(MyFiles)
            
    Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
                On Error GoTo 0
    
    Sheets("FX Historical Data").Activate
    RowCount = Range("A1").End(xlDown).Row
    FXArr = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
    
    Sheets("Position Data").Activate
    RowCount = Range("A1").End(xlDown).Row
    PositionArr = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
    
    ActiveWorkbook.Close

Next FNum
End If
    
    Sheets("FX Historical Data").Activate
    Range(Range("A2"), Range("b2").Offset(UBound(FXArr, 1) - 1)) = FXArr
    
    Sheets("Position Data").Activate
    Range(Range("A2"), Range("d2").Offset(UBound(PositionArr, 1) - 1)) = PositionArr
   
    
    Sheets("Report").Activate
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I only needed to know...

ReDim Preserve MyFiles(1 To FNum)

try change to

    If FNum > 0 Then
        Dim Arr
        ReDim Arr(1 To FNum, 1 To 2)
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Workbooks.Open(FilePath & MyFiles(FNum))
            Sheets("FX Historical Data").Activate
            RowCount = Range("A1").End(xlDown).Row
            Arr(FNum, 1) = Range(Range("A2"), Range("b2").Offset(RowCount - 1))
        
            Sheets("Position Data").Activate
            RowCount = Range("A1").End(xlDown).Row
            Arr(FNum, 2) = Range(Range("A2"), Range("d2").Offset(RowCount - 1))
        
            ActiveWorkbook.Close
    
        Next FNum
        For FNum = LBound(Arr) To UBound(Arr)
            Sheets("FX Historical Data").Activate
            Range("A2").Resize(UBound(Arr(FNum, 1), 1), UBound(Arr(FNum, 1), 2)) = Arr(FNum, 1)
            Sheets("Position Data").Activate
            Range("A2").Resize(UBound(Arr(FNum, 2), 1), UBound(Arr(FNum, 2), 2)) = Arr(FNum, 2)
        Next
    
    Sheets("Report").Activate
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic