Copy data from sheet to another

Status
Not open for further replies.

farhad

New Member
Joined
May 18, 2009
Messages
41
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
- Run-time error '9': Subscript out of range - outarr(l + 49, n) = inarr2(j, l)(Refer to this line of code)

Thanks

VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Status
Not open for further replies.

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top