Copy Row from one Spreadsheet to another

[Annmariehalligan]

Hi

Looking for help please. New to VBA.

I need help in writing VBA: I have two separated workbooks which are logs of information which have approx 16 column in each ; both logs are shared with several people inputting data throughout the day to each. Log 1 and Log 2 do not have same headings in same columns. I need to assign a macro to each sheet that copies the row (but only certain column headings) when the value in e.g Cell H2 is updated to 'complete' (selected from a drop-down list).

This row is then to be copied to the last row in a separate workbook called 'master-sheet of complete cases' sheet1

Log 1 need to copy row but only cells in col A:D G:H
Log 2 need to copy row but only cells in col A:C E:H

Any help appreciated; thanks and regards AMH

 

[Eloise T]

It might be easier if you upload a "sanitized" copy of each of the two log files and show the results you are expecting.

 

[GraH - Guido]

Annmariehalligan, I've repeated it many times before on this forum, Power Query might be an alternative to macro if you are not a programmer. Depending on your Excel version it is possibly natively onboard (get & transform as of 2016 version).
PQ is a list of applied steps, one can "record" via simple mouse clicks. With the advantage of seeing your intermediate result in the preview screen.
In this case the rough steps would be:
- read first xlsx-file -> new Query, from file -> .xlsx
- filter table on "complete"
- select the required columns (as you would in Excel, and press "remove other columns")
- load this first as "connection only"
- repeat the same for LOG2
- Open first query (LOG1) and select "append as new" -> creates a 3rd query
- select from drop down in the pop-up screen the second query
- load this 3rd query as table in Excel -> it will contain all filtered data from both logs for the selected columns.

Whenever log1/2 have changed, just press refresh (right mouse click) on that loaded table.
Many good video tutorials are available.

 

[Annmariehalligan]

It might be easier if you upload a "sanitized" copy of each of the two log files and show the results you are expecting.

Thank you Eloise Attached are the files

 

[Annmariehalligan]

Annmariehalligan, I've repeated it many times before on this forum, Power Query might be an alternative to macro if you are not a programmer. Depending on your Excel version it is possibly natively onboard (get & transform as of 2016 version).
PQ is a list of applied steps, one can "record" via simple mouse clicks. With the advantage of seeing your intermediate result in the preview screen.
In this case the rough steps would be:
- read first xlsx-file -> new Query, from file -> .xlsx
- filter table on "complete"
- select the required columns (as you would in Excel, and press "remove other columns")
- load this first as "connection only"
- repeat the same for LOG2
- Open first query (LOG1) and select "append as new" -> creates a 3rd query
- select from drop down in the pop-up screen the second query
- load this 3rd query as table in Excel -> it will contain all filtered data from both logs for the selected columns.

Whenever log1/2 have changed, just press refresh (right mouse click) on that loaded table.
Many good video tutorials are available.

THank you for your reply I will certainly consider PowerQuery