Sheet 1 Data A Sheet 1 Data B Sheet 1 Data C Sheet 1 Data D
79 22 43
19 28 16
79 75 86
96 82 90
99 23 65
32 94 23
8 67 55
22 61 41
97 93 81
98 43 93
80 87 60
84 3 81
44 3 37
31 85 32
71 78 49
18 31 16
Sheet 2 Data A Sheet 2 Data B Sheet 2 Data C Sheet 2 Data D
54 78 41
38 68 92
74 44 90
1 77 49
34 45 44
60 45 59
80 84 87
13 15 94
71 65 98
54 49 63
Sheet 3 Data A Sheet 3 Data B Sheet 3 Data C Sheet 3 Data D
73 99 51
68 66 63
54 75 12
44 10 25
62 47 35
29 54 31
9 48 92
50 59 10
Sheet 4 Data A Sheet 4 Data B Sheet 4 Data C Sheet 4 Data D
49 28 84
38 76 47
23 49 15
Sub LoopAndAddChangedData()
'This code loops through multiple sheets checking for changes between the
'"Source" file and the "Destination" file...any changes (new OR changed)
'are wrtitten to the destination file as a new entry.
'Both files are identical in structure
Dim MySourceFile As String 'Holds the name of the source file
Dim MyDestFile As String 'Holds the name of the destination file
Dim MySheetName As String 'Holds the name of the sheet
Dim OuterLoop As Integer 'Outer loop (for sheets)
'Load the names of the source and destination files
MySourceFile = "Sean Source File"
MyDestFile = "Sean Destination File"
'Go to the sourcefile.
Workbooks(MySourceFile).Activate
'Outer Loop to add formulas(to cycle through the sheets)...
'...the "-1" is for the "Get your code here" sheet
For OuterLoop = 1 To Workbooks(MySourceFile).Sheets.Count - 1
'Select the appropriate sheet
Sheets(OuterLoop).Select
'Get the active sheet name
MySheetName = ActiveSheet.Name
'Add the concatenation block formula to source sheet
'Recorded macro formula reads "=CONCATENATE(A2,B2,C2,D2)"
Workbooks(MySourceFile).Sheets(MySheetName).Range("E2:E" & _
Workbooks(MySourceFile).Sheets(MySheetName).Range("A65000").End(xlUp).Row). _
Formula = "=CONCATENATE(RC[-4],RC[-3],RC[-2],RC[-1])"
'Add the concatenation block formula to destination sheet
'Recorded macro formula reads "=CONCATENATE(A2,B2,C2,D2)"
Workbooks(MyDestFile).Sheets(MySheetName).Range("E2:E" & _
Workbooks(MyDestFile).Sheets(MySheetName).Range("A65000").End(xlUp).Row). _
Formula = "=CONCATENATE(RC[-4],RC[-3],RC[-2],RC[-1])"
'Add the "Check" block formula to source sheet
'Recorded macro formula reads...
'"=IFERROR(MATCH(E2,'[Sean Destination File.xlsx]Sheet1'!$E:$E,0),"Update")
Workbooks(MySourceFile).Sheets(MySheetName).Range("F2:F" & _
Workbooks(MySourceFile).Sheets(MySheetName).Range("A65000").End(xlUp).Row). _
Formula = "=IFERROR(MATCH(RC[-1],'[" & MyDestFile & ".xlsx]" & MySheetName & "'!C5,0),""Update"")"
'Now, anything that's changed says "Update" in column F
'Go to start of range
Range("F2").Select
'Inner loop (to go down the lines of data
Do While ActiveCell.Value <> ""
'Logical test to see if this is an "Update" line
If ActiveCell.Value = "Update" Then
'Copy this entire line to the bottom...
'...of the corresponding sheet in the destination file
ActiveCell.EntireRow.Copy _
Workbooks(MyDestFile).Sheets(MySheetName).Range("A65000").End(xlUp).Offset(1, 0)
'Move down a cell
ActiveCell.Offset(1, 0).Select
Else
'Move down a cell
ActiveCell.Offset(1, 0).Select
End If
Loop
'Clear the formulas
Workbooks(MySourceFile).Sheets(MySheetName).Range("E:F").ClearContents
Workbooks(MyDestFile).Sheets(MySheetName).Range("E:F").ClearContents
Next OuterLoop
End Sub
