Option Explicit
Sub CopyPasteWorksheets()
Dim wbDec As Workbook, wbJune As Workbook, wbAnalysis As Workbook
Dim wsDec As Worksheet, wsJune As Worksheet
Dim PresPres As Worksheet, PresAbs As Worksheet, AbsPres As Worksheet
'Stop screen from updating to speed things up
Application.ScreenUpdating = False
Application.EnableEvents = False
'Add 3 new worksheets. They each represent a different category, namely the one with already existing insurances, one with new insurances
'and one with the insurances that are closed due to mortality, lapse or maturity. Add two (temporary) worksheets to paste the databases.
Worksheets.Add().Name = "PresPres"
Worksheets.Add().Name = "PresAbs"
Worksheets.Add().Name = "AbsPres"
Worksheets.Add().Name = "DataDec"
Worksheets.Add().Name = "DataJune"
'Define the active workbook
Set wbAnalysis = ThisWorkbook
'Define the first database. Copy/paste the sheet and close them afterwards.
Set wbDec = Workbooks.Open(Filename:="F:\Risk_Management_2\Embedded_Value\2015\20151231\Data\DLL\Master Scala\Extract.xlsx")
wbDec.Sheets("SCALA").Range("A1").CurrentRegion.Copy
wbAnalysis.Sheets("DataDec").Range("A1").PasteSpecial xlPasteValues
wbDec.Close
'We have to do the same for the other database. We cannot do it at the same time, because both files have the same name,
'and can't be opened at the same time.
Set wbJune = Workbooks.Open(Filename:="F:\Risk_Management_2\Embedded_Value\2016\20160630\Data\DLL\Master Scala\extract.xlsx")
wbJune.Sheets("SCALA").Range("A1").CurrentRegion.Copy
wbAnalysis.Sheets("DataJune").Range("A1").PasteSpecial xlPasteValues
wbJune.Close
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub Compare()
Dim DataDec As Worksheet, DataJune As Worksheet
Dim lastRowDec As Long
Dim lastRowJune As Long
Dim lastRowPresAbs As Long
Dim lastRowPresPres As Long
Dim lastRowAbsPres As Long
Dim foundTrue As Boolean
Dim i As Long, j As Long, k As Long, l As Long
'Define the last row of the different sheets
lastRowDec = Sheets("DataDec").Cells(Sheets("DataDec").Rows.Count, "B").End(xlUp).Row
lastRowJune = Sheets("DataJune").Cells(Sheets("DataJune").Rows.Count, "B").End(xlUp).Row
lastRowPresAbs = Sheets("PresAbs").Cells(Sheets("PresAbs").Rows.Count, "B").End(xlUp).Row
lastRowPresPres = Sheets("PresPres").Cells(Sheets("PresPres").Rows.Count, "B").End(xlUp).Row
lastRowAbsPres = Sheets("AbsPres").Cells(Sheets("AbsPres").Rows.Count, "B").End(xlUp).Row
'Compare the file numbers in column B of both sheets. If they are the same, copy/paste the entire row to sheet PresPres,
'if they are not, copy/paste the entire row to sheet PresAbs.
For i = 1 To lastRowDec
foundTrue = False
For j = 1 To lastRowJune
If Sheets("DataDec").Cells(i, 1).Value = Sheets("DataJune").Cells(j, 1).Value Then
foundTrue = True
Sheets("PresPres").Rows(lastRowPresPres + 1) = Sheets("DataDec").Rows(i)
lastRowPresPres = lastRowPresPres + 1
Exit For
End If
Next j
If Not foundTrue Then
Sheets("DataDec").Rows(i).Copy Destination:= _
Sheets("PresAbs").Rows(lastRowPresAbs + 1)
lastRowPresAbs = lastRowPresAbs + 1
End If
Next i
'Look if there are file numbers that are only present in June's database. If so, copy/paste entire row to sheet AbsPres.
For k = 1 To lastRowJune
foundTrue = False
For l = 1 To lastRowDec
If Sheets("DataJune").Cells(k, 1).Value = Sheets("DataDec").Cells(l, 1).Value Then
foundTrue = True
Exit For
End If
Next l
If Not foundTrue Then
Sheets("DataJune").Rows(k).Copy Destination:= _
Sheets("AbsPres").Rows(lastRowAbsPres + 1)
lastRowAbsPres = lastRowAbsPres + 1
End If
Next k
'Stop screen from updating to speed things up.
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
