VBA: Combine data within the respective sheets

[Aberdham]

Hi All,

So I have a workbook with 3 type of invoices within 3 sheets: (Historical; Sales cost; Purchase cost)
each sheet has a unique identifier as H,R,P in column A;
in Column J e.g. Machinery, so far we have 75 machinery, therefore 75 Sheets.

I was able to pull all the data from all the 3 invoice Sheets into overview machinery 1 with the following code:

Public Sub CombineDataFromAllSheets()
Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long

'Set references up-front
Set wksDst = ThisWorkbook.Worksheets("Overview Machinery 1")
lngDstLastRow = LastOccupiedRowNum(wksDst)
lngLastCol = LastOccupiedColNum(wksDst)

'Set the destination range
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

'Looping through
For Each wksSrc In ThisWorkbook.Worksheets

If wksSrc.Name <> "Overview Machinery 1" Then

lngSrcLastRow = LastOccupiedRowNum(wksSrc)

'Store all relevant source data then copy it to the destination range
With wksSrc
Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
rngSrc.Copy Destination:=rngDst
End With

'Redefine the destination range that new data has been added
lngDstLastRow = LastOccupiedRowNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)

End If

Next wksSrc
End Sub
'INPUT : Sheet, the worksheet we are going to search to find the last row
'OUTPUT : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return as 1
Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
Dim lng As Long
If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
With Sheet
lng = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
Else
lng = 1
End If
LastOccupiedRowNum = lng
End Function
'INPUT : Sheet, the worksheet we are going search to find the last column
'OUTPUT : Long, the last occupied column
'SPECIAL CASE: if Sheet is empty, return as 1
Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
Dim lng As Long
If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
With Sheet
lng = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End With
Else
lng = 1
End If
LastOccupiedColNum = lng
End Function

I would like to know how to pull the necessary data from the 3 invoice Sheets to the respective overview machine Sheets (1-75) based on column J?

Furthermore, this is probably a silly question, But I would like to ask that whenever i have new entries, do I have to run the macro again and delete the duplicate ones to include the new entries? or is there a way to modify the code to automatcally update to the respective machinery Sheets?

Please let me know if you Need further clarifications!

Any help would be greatly appreciated !

Regards,
M
[TABLE="width: 983"]
<tbody>[TR]
[TD]sheet 1 (historical):[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice type[/TD]
[TD]Invoice Number[/TD]
[TD]Supplier/Debitor[/TD]
[TD]Description[/TD]
[TD]Invoice Date[/TD]
[TD]FX rate[/TD]
[TD]USD Amount[/TD]
[TD]EUR Amount[/TD]
[TD]Change in Inventory[/TD]
[TD]Machinery[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]K8554214[/TD]
[TD]ADA[/TD]
[TD]Deposit SN 844451[/TD]
[TD]1-12-2017[/TD]
[TD]1,1885[/TD]
[TD]$361.067,54[/TD]
[TD]303.801,05 €[/TD]
[TD]-303.801,05 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]K8554215[/TD]
[TD]ADA[/TD]
[TD]final payment_ESN 848462[/TD]
[TD]1-1-2018[/TD]
[TD]1,1993[/TD]
[TD]$358.718,75[/TD]
[TD]299.106,77 €[/TD]
[TD]-299.106,77 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]K8554216[/TD]
[TD]APOM[/TD]
[TD]final payment_ESN 848462[/TD]
[TD]2-2-2018[/TD]
[TD]1,2492[/TD]
[TD]$ 2.600,60[/TD]
[TD]2.081,82 €[/TD]
[TD]-2.081,82 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]85426589[/TD]
[TD]APOM[/TD]
[TD]inspection[/TD]
[TD]2-2-2018[/TD]
[TD]1,2492[/TD]
[TD]$ 3.461,33[/TD]
[TD]2.770,84 €[/TD]
[TD]-2.770,84 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]85426589[/TD]
[TD]UIJ[/TD]
[TD]opmen[/TD]
[TD]2-2-2018[/TD]
[TD]1,2492[/TD]
[TD]$ 18.988,94[/TD]
[TD]15.200,88 €[/TD]
[TD]-15.200,88 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2 (sales)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type of invoice[/TD]
[TD]Invoice Number[/TD]
[TD]Supplier/Debitor[/TD]
[TD]Description[/TD]
[TD]Invoice Date[/TD]
[TD]FX rate[/TD]
[TD]USD Amount[/TD]
[TD]EUR Amount[/TD]
[TD]Change in Inventory[/TD]
[TD]Machinery[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]AR00214522[/TD]
[TD]ADA[/TD]
[TD]AR00251452[/TD]
[TD]11-4-2018[/TD]
[TD]1,2384[/TD]
[TD]$ 15.222,00[/TD]
[TD]€ 12.291,67[/TD]
[TD]-12.291,67 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sheet 3( purchase)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type of invoice[/TD]
[TD]Invoice Number[/TD]
[TD]Supplier/Debitor[/TD]
[TD]Description[/TD]
[TD]Invoice Date[/TD]
[TD]FX rate[/TD]
[TD]USD Amount[/TD]
[TD]EUR Amount[/TD]
[TD]Change in Inventory[/TD]
[TD]Machinery[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]58485[/TD]
[TD]AAD[/TD]
[TD]AP001523[/TD]
[TD]11-4-2018[/TD]
[TD]1,2384[/TD]
[TD]$ 15.222,00[/TD]
[TD]€ 12.291,67[/TD]
[TD]12.291,67 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]584885[/TD]
[TD]AAR[/TD]
[TD]AP001524[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$ 1.600,00[/TD]
[TD]€ 1.298,60[/TD]
[TD]1.298,60 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]584882[/TD]
[TD]AAE[/TD]
[TD]AP001525[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$ 500,00[/TD]
[TD]€ 405,81[/TD]
[TD]405,81 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]48595[/TD]
[TD]AES[/TD]
[TD]AP001526[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$ 18.455,00[/TD]
[TD]€ 14.978,49[/TD]
[TD]14.978,49 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]485953[/TD]
[TD]AHJ[/TD]
[TD]AP001527[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$ 16.746,00[/TD]
[TD]€ 13.591,43[/TD]
[TD]13.591,43 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]1007019[/TD]
[TD]UIJ[/TD]
[TD]AP001528[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$ 6.200,00[/TD]
[TD]€ 5.050,51[/TD]
[TD]5.050,51 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]1007020[/TD]
[TD]JIMK[/TD]
[TD]AP001529[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$ 35.000,00[/TD]
[TD]€ 28.434,48[/TD]
[TD]28.434,48 €[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]8958952[/TD]
[TD]KYT[/TD]
[TD]AP001530[/TD]
[TD]1-4-2018[/TD]
[TD]1,2321[/TD]
[TD]$2.000.000,00[/TD]
[TD]€ 1.617.992,07[/TD]
[TD]1.[/TD]
[TD] [/TD]
[TD]

[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]