Sub Process_Report()
Dim wkb As Workbook
Dim Sheet1 As Worksheet, Sheet2 As Worksheet, Sheet3 As Worksheet, Sheet4 As Worksheet
Dim Sheet1ClaimNumCol As Long, Sheet1Total1Col As Long, Sheet1Total2Col As Long, Sheet1Total3Col As Long, Sheet1LastRow As Long
Dim Sheet2ClaimNumCol As Long, Sheet2DescCol As Long, Sheet2Code1Col As Long, Sheet2LastRow As Long
Dim Sheet3ClaimNumCol As Long, Sheet3NumbersCol As Long, Sheet3PercentageCol As Long, Sheet3LastRow As Long
Dim Sheet4ClaimNumCol As Long, Sheet4Total1Col As Long, Sheet4Total2Col As Long
Dim Sheet4Total3Col As Long, Sheet4DescCol As Long, Sheet4Code1Col As Long, Sheet4NumbersCol As Long
Dim Sheet4PercentageCol As Long, Sheet4LastRow As Long
Dim myClaim As Variant, myClaimCheck As Variant
Dim myTotal1 As Variant, myTotal2 As Variant, myTotal3 As Variant, myDesc As Variant
Dim myCode1 As Variant, myNumbers As Variant, myPercentage As Variant
Set wkb = ThisWorkbook
Set Sheet1 = wkb.Worksheets("Sheet1")
Set Sheet2 = wkb.Worksheets("Sheet2")
Set Sheet3 = wkb.Worksheets("Sheet3")
Set Sheet4 = wkb.Worksheets("Sheet4")
'=================================================================================================
'=============Define Variables=================================================================
'======= Sheet1 ==================================
With Sheet1.Rows(1)
'.Select
Sheet1ClaimNumCol = .Find(What:="Claim Number", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet1Total1Col = .Find(What:="Total1", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet1Total2Col = .Find(What:="Total2", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet1Total3Col = .Find(What:="Total3", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet1LastRow = .Cells(Rows.Count, Sheet1ClaimNumCol).End(xlUp).Row
End With
'======= Sheet2 ==================================
With Sheet2.Rows(1)
'.Select
Sheet2ClaimNumCol = .Find(What:="Claim Number", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet2DescCol = .Find(What:="Description", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet2Code1Col = .Find(What:="Code 1", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet2LastRow = .Cells(Rows.Count, Sheet2ClaimNumCol).End(xlUp).Row
End With
'======= Sheet3 ==================================
With Sheet3.Rows(1)
'.Select
Sheet3ClaimNumCol = .Find(What:="Claim Number", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet3NumbersCol = .Find(What:="Numbers", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet3PercentageCol = .Find(What:="Percentage", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet3LastRow = .Cells(Rows.Count, Sheet3ClaimNumCol).End(xlUp).Row
End With
'======= Sheet4 ==================================
With Sheet4.Rows(1)
Sheet4ClaimNumCol = .Find(What:="Claim Number", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4Total1Col = .Find(What:="Total1", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4Total2Col = .Find(What:="Total2", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4Total3Col = .Find(What:="Total3", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4DescCol = .Find(What:="Description", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4Code1Col = .Find(What:="Code 1", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4NumbersCol = .Find(What:="Numbers", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4PercentageCol = .Find(What:="Percentage", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
Sheet4LastRow = .Cells(Rows.Count, Sheet4ClaimNumCol).End(xlUp).Row
End With
'========Determine unique list of claim numbers from all 3 tabs===========================
With Sheet1
.Select
.Range(Cells(2, Sheet1ClaimNumCol), Cells(Sheet1LastRow, Sheet1ClaimNumCol)).Copy
End With
With Sheet4
.Cells(Sheet4LastRow + 1, Sheet4ClaimNumCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheet4LastRow = .Cells(Rows.Count, Sheet4ClaimNumCol).End(xlUp).Row
End With
With Sheet2
.Select
.Range(Cells(2, Sheet2ClaimNumCol), Cells(Sheet2LastRow, Sheet2ClaimNumCol)).Copy
End With
With Sheet4
.Select
.Cells(Sheet4LastRow + 1, Sheet4ClaimNumCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheet4LastRow = .Cells(Rows.Count, Sheet4ClaimNumCol).End(xlUp).Row
End With
With Sheet3
.Select
.Range(Cells(2, Sheet3ClaimNumCol), Cells(Sheet3LastRow, Sheet3ClaimNumCol)).Copy
End With
With Sheet4
.Select
.Cells(Sheet4LastRow + 1, Sheet4ClaimNumCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheet4LastRow = .Cells(Rows.Count, Sheet4ClaimNumCol).End(xlUp).Row
End With
With Sheet4
.Columns("A:A").RemoveDuplicates Columns:=Sheet4ClaimNumCol, Header:=xlYes
End With
'============Loop through each sheet, set values on 4th sheet ==============================
For i = 2 To Sheet4LastRow
myClaim = Sheet4.Cells(i, Sheet4ClaimNumCol).Value
myTotal1 = ""
myTotal2 = ""
myTotal3 = ""
myDesc = ""
myCode1 = ""
myNumbers = ""
myPercentage = ""
With Sheet1
For j = 2 To Sheet1LastRow
myClaimCheck = .Cells(j, Sheet1ClaimNumCol).Value
If myClaim = myClaimCheck Then
myTotal1 = .Cells(j, Sheet1Total1Col).Value
myTotal2 = .Cells(j, Sheet1Total2Col).Value
myTotal3 = .Cells(j, Sheet1Total3Col).Value
End If
Next j
End With
With Sheet2
For k = 2 To Sheet2LastRow
myClaimCheck = .Cells(k, Sheet2ClaimNumCol).Value
If myClaim = myClaimCheck Then
myDesc = .Cells(k, Sheet2DescCol).Value
myCode1 = .Cells(k, Sheet2Code1Col).Value
End If
Next k
End With
With Sheet3
For l = 2 To Sheet3LastRow
myClaimCheck = .Cells(l, Sheet3ClaimNumCol).Value
If myClaim = myClaimCheck Then
myNumbers = .Cells(l, Sheet3NumbersCol).Value
myPercentage = .Cells(l, Sheet3PercentageCol).Value
End If
Next l
End With
With Sheet4
Cells(i, Sheet4Total1Col) = myTotal1
Cells(i, Sheet4Total2Col) = myTotal2
Cells(i, Sheet4Total3Col) = myTotal3
Cells(i, Sheet4DescCol) = myDesc
Cells(i, Sheet4Code1Col) = myCode1
Cells(i, Sheet4NumbersCol) = myNumbers
Cells(i, Sheet4PercentageCol) = myPercentage
End With
Next i
End Sub
