Help on how to Compare five Excel workbooks and copying matched data to a new workbook

Welcome to the forum


Can you clarify:

1. Do you want each block of 5 rows to have headers?
2. Should each block of 5 rows be separated by a blank row?
3. Does a name in columnA only appear once in any worksheet?

thanks

Try this:
- Put attached workbook in same folder as the 5 files
- Amend workbook names in this line (enclose name in " ", and do not include extension) :

Please Login or Register  to view this content.

- run with {CTRL} t

Please Login or Register  to view this content.

Q1 where do you want to save the new file? What is the full path? It is currently set to save to same folder as Master file - so you can leave it as it is if you want.

So if you want to save it to C:\Documents\Excel

Please Login or Register  to view this content.

Note - the code assumes the other 5 workbooks are in the same folder - so you will need to move them or amend the code so that VBA knows where to find them

Q2 what do you want to call the new workbook?
If you want to name it Consol then

Please Login or Register  to view this content.

The bit after the "&" is a timestamp -so you can ignore that if you want
The file automatically receives extension .xlsx when saved

No -the problem is probably elsewhere, but ....
- you could try putting in this line BEFORE that line and see if the message box tells you something - is the path valid?

Please Login or Register  to view this content.

If that does not provide any clues:
I suggest we go one stage back and let's prove that the code works for you BEFORE you adjust to match your folder structure
So go back to my original code
Amend only the file names
Create a test folder and put a copy of each of the 5 files there
Also put Master.xlsm in that folder
Next, amend this line to match the 5 file names

Please Login or Register  to view this content.

Please confirm that the 5 files have extensions .xlsx ( this is what the code is expecting)

If the code still breaks down at the same line, then there must be a minor difference between Excel 2007 (Is that what you are using?) and Excel 2016 - I will make a modification tomorrow if that is the case

Let me know if it worked or not - I will be back in the morning
If your code does not work, please post your code in full enclosed in code tags - click on # icon and
[ CODE]code here[ /CODE]

Hi,
1. I used the original code amending only the line wbNamesArr = Array("F1", "F2", "F3", "F4", "F5"), but it's given me the following error:
and when I click on debug, The following line is highlighted.


Set wb = Workbooks.Open(wbName)

2. I put the code MsgBox myPath & "\" & newFileName before the line wbNew.SaveAs (myPath & "\" & newFileName) but it's showing the same error.

as requested in post#10
if your code does not work, please post your code in full enclosed in code tags - click on # icon and
[ CODE ]paste code here[ /CODE ]

Does the file Arbalet2.xlsx exist in that folder ?
Is it Arbalet2.xls perhaps ?

You are confusing me totally here

1. What are the 5 file names
2. What is the full folder path?

I created new folder C:\Documents\HR_ProfileBooks
I put Master.xlsm and the 5 files in that folder
I amended the code to match
I ran the code
It works without any issues

- 2 lines only amended in the code

Please Login or Register  to view this content.

My files have 2 columns A with Nmaes and B with file name, only the first 2 names are present in all files

Result
combine5files01.jpg


Please compare the code below and tell me what was different in your code - it should match EXACTLY

Please Login or Register  to view this content.

Those lines are comments only - so are not part of the code (- anything starting with apostrophe is a comment)

Try simply using my code (per post#20) instead of yours
(There may something small you are missing)

Let me know how you get on

The code now is running without any errors, but the report file only copying the headers rows (Columns Title) without any matched name or any name data (Row) .

Result.PNG

The next thing to do is to stop the code running after all values have been copied to new workbook and look at those values.
So amend the code:
Before the line that begins 'Remove all rows where count of names.... insert this line

Please Login or Register  to view this content.

Column A contains the CountIf formula - for every name that appears in all 5 files there should be the value 5 in column A. Every row without 5 in column A is subsequently deleted - so this is most likely where the problem lies.

Let me know what you find...
(All values from all files should be there with the headers appearing once only)

Here are the 5 files that I used to test - there's most likely a minor difference between the layout of your files and mine
(or Excel 2007 is behaving differently to Excel 2016 somewhere - I do not think that is what it is because all the commands & functions used were present in Excel 2007)

I suggest you:
- create a new test folder and put my test files in that folder
- leave Master.xlsm in its original folder
- amend the path in the vba to match the new folder
- remove the "Exit Sub" line if it is still in there per my instruction in post#24
- run the macro - it should work
- if it does not work then there must be a minor difference somewhere between 2007 and 2016

The next step is to work out (together) what to change
Are you available at 12 noon today? and then we can be online at same time and I will tell you exactly how to test each stage


For info - this is what the macro does step by step:
creates a new file

opens file "List_1"
adds header row to new file (values placed in row 1)
copies sheet values (rows 2 to last row with value in column A)
pastes to new file to first empty row below existing data (again based on columnA) - in this case it will be row 2
closes "List_1"

opens "Arba_let2"
copies sheet values (rows 2 to last row with value in column A)
pastes to new file to first empty row below existing data (again based on columnA) - in this case it will be row 2
closes "Arba_let2"

same as above for "Expedia1" "Expedia2" "Book3"

inserts temporary working columns (A & B)
insert temporary formulas
columnA COUNTIF(C2:C999,C2) - where 999 is the last used row
columnB contains a sequential number - this actually isn't being used - I thought it may have been required for sorting but I did not need to use it

removes all rows where count of names is not equal to 5
- uses autofilter and looks at values in columnA
- all rows where values in column A not equal to 5 are displayed
- all visible rows are deleted
- autofilter set to off which displays all remaining values (ie those equal to 5 in column A)

sorts data by name

inserts header for each "name" and a blank row in between each name block

deletes temporary columns

Ignore my last post for the moment - our post have crossed - will get back to you in 30 minutes

This problem was caused by your first post - these are your words - you said ALL of the files

one row from each workbook for the same name if match found in all of them

Will send you revised code in 30 minutes......

Use the full code below and amend newFileName value to whatever you are using

Amendments made to code (with explanations):

1. this line speeds up the running of the code - screen is now only updated at the end of the macro when the same property is reverted to true

Please Login or Register  to view this content.

2. this filters to show only those rows where the name count = 1 (these rows are subsequently deleted)

Please Login or Register  to view this content.

3. this bit of code:
- starts with the last row and
- works backwards through name values (names are currently in column C due to temporary formulas)
- blank row and headers inserted if name differs from name above
- does not need to do that at row 2 because the header is already there (Exit For tells vba to exit the current For loop)
- where possible always step backwards through the rows when deleting or inserting rows
(if delete/insert starting at top then row numbers below are changed each time and that would have to be built into the VBA - "i" would require resetting)

Please Login or Register  to view this content.

full code:

Please Login or Register  to view this content.

After testing the above code AND getting it to work, then to add in the borders

place the code below immediately below this line:

Please Login or Register  to view this content.

The code works as follows:
c = last column
check to see if there is anything in each cell in column A If Not IsEmpty(.Cells(r, 1)) Then-
put border around cells in columns 1 to c in that row

code:

Please Login or Register  to view this content.

I edited the code but this is how the page looks

Result1.PNG Result2.PNG.

Also no source file name anywhere like my post 25

I spotted a small error in the code

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Please amend it and test again
thanks

I put the code for the border and added

Please Login or Register  to view this content.

but I still have a problem with the page:

Result3.PNG

Please attach your 5 files - I will get the code to work with your files