VBA: Combine data within the respective sheets

A

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
163
Office Version
  1. 365
Platform
  1. Windows
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]
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You must log in or register to reply here.

Similar threads

M
  • Question Question
Consolidate Total From Multiple Worksheets to Main Worksheet
Replies
6
Views
397
Alex Blakenburg
A
P
  • Question Question
Pulling data from a workbook with a different sheet name
Replies
0
Views
632
pragmatic123
P
P
  • Question Question
Pull Data from multiple workbooks and transpose data side by side
Replies
0
Views
670
pragmatic123
P
D
  • Question Question
Help me selecting VBA Loop through All Sheets Except the main Sheet
Replies
6
Views
3K
mumps
mumps
M
  • Solved
VBA copy conditional Formatting
Replies
7
Views
7K
Alex Blakenburg
A

Forum statistics

Threads
1,224,817
Messages
6,181,144
Members
453,021
Latest member
Justyna P

Share this page

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