Copy data from sheet to another

[farhad]

Good Day

I have a sheet of 7000 rows that I update and would like the information to be copied of the updated data to another sheet. A solution was provided on this forum Copy Data from One to Another based on Reference Number which works however i added more columns and since then the code does work . I tried adapting the code to accommodate the extra columns. The code is as follows: Error I am getting is as follows:

I have posted this already on

Copy Data from One to Another based on Reference Number

Good Day, My query is from a previous thread that has been resolved and many thanks to John Tropley for the solution. My new query based on John's solution which works, SInce I have amended the spreadsheet to include more columns and adapt the code and when I run the macro from John's...

www.excelforum.com

- Run-time error '9': Subscript out of range - outarr(l + 49, n) = inarr2(j, l)(Refer to this line of code)

Thanks

Sub Change_File()


Dim inarr1(), inarr2(), outarr()
Dim rng As Range, destination As Range
Dim i As Long, j As Long

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set ws1 = Worksheets("FARJUL")
Set ws2 = Worksheets("FARJUN")
Set ws3 = Worksheets("Change_Log") ' this is not needed



lastrow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row  ' FARJUL
inarr1 = ws1.Range("A9:AW" & lastrow1)

lastrow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row  ' FARJUN
inarr2 = ws2.Range("A9:AW" & lastrow2)

ReDim outarr(1 To 49, 1 To 1)

'
' 2 dimensional array
'
n = 0

For i = 1 To UBound(inarr1, 1) ' Loop through FARFUL records row

    For j = 1 To UBound(inarr2, 1) ' Loop through FARFUN records row
    
    If inarr1(i, 49) = inarr2(j, 49) Then   ' References match
    
        For k = 1 To 49 ' Check if any fields changed
        
         If inarr1(i, k) <> inarr2(j, k) Then  ' Change
             n = n + 1
             ReDim Preserve outarr(1 To 49, 1 To n)  'Add to change log
             For l = 1 To 49
                outarr(l, n) = inarr1(i, l)
                outarr(l + 49, n) = inarr2(j, l)
                inarr2(j, l) = inarr1(i, l)
             Next l
                GoTo nexti
           End If
           
         Next k
          GoTo nexti
          
     End If
     
    Next j
nexti:
Next i
'
'  Output Change log array
'
Set destination = ws3.Range("A3")
destination.Resize(UBound(outarr, 2), UBound(outarr, 1)).Value = Application.Transpose(outarr)
'
'  Output Changed FARJUN
'
Set destination = ws2.Range("A9")
destination.Resize(UBound(inarr2, 1), UBound(inarr2, 2)).Value = inarr2

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True



End Sub

 

[Fluff]

Duplicate to: Copy Data from one Sheet to another as Data is updated

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.