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