Comparing only the Column Headers between the two workbooks

[sandeepgoud88]

I have two workbooks where i need to compare both column headers.

Ex: the workbook1 has following columns:

columnA | columnB | columnC

the workbook2 has following columns

columnA | columnB | columnC | columnD | columnE

output in workbook1 should be

columnA | columnB | columnC | columnD | columnE..

Can someone help me with the above challenge....

[Mumps1]

From your example, it seem that you simply want to copy the headers from the workbook that has the most headers. When you say "compare" what do you mean? Are the headers in row 1 of both workbooks and what are the sheet names containing the headers in each workbook? Perhaps you could attach a copy of you workbooks and then we can easily see what we are dealing with.

[sandeepgoud88]

Thanks for your response here is my requirement. please let me know if you need any other information.

I have two excel files (Workbook1 and workbook2)they both should be having same columns headers and sheet names once i run the vba code. if am adding any new column or any new sheet in workbook1. workbook2 should automatically get the updated with column headers and sheets missing in workbook2 in comparison with workbook1

Challenge here is i dont need any data to be copied from the workbook1 i only need column headers and sheets to be added in workbook2.

Exepected Result: after running code in workbook2. workbook2 should have the same number of sheets and same columns in all the sheets like workbook1.

[Mumps1]

Do all the sheets in both workbooks contain data in all the columns? Does workbook1 always have more or less number of sheets than workbook2? Could you please attach a copy of both files?

[sandeepgoud88]

i have attached copy of two workbooks..

Workbook1 is the main source file and it many contain more or less sheets than workbook2.

1.if workbook1 contains less sheets or columns then we should be adding the missing sheets or columns in the workbook1 referring workbook2.

2. if workbook2 contains less sheets or columns then we should be adding the missing sheets or columns in the workbook2 referring workbook1.

3. after running the code both the workbooks should have same column headers in all the sheets and same sheets should be present in both the workbooks.

4. we dont need to copy any data under the headers we only need to add the missing sheets and headers in each sheet.

thanks in advance.

[mikerickson]

Do you require the sheets and headers to be in the same order as the other book? (If so which book controls the order)

Do you want this to be the result of a button push, or do you want it to be automatic, updating as the user adds/ deletes columns or sheets?

[sandeepgoud88]

Workbook1 would be the source and if we can get the sheets and headers in the same order that would be really great. even if we don't that should be fine...

i assume button push would be the best option and it should be present on workbook2 to kick off the code.

thanks in advance

[sandeepgoud88]

can someone please help me with the above challenge....

[Mumps1]

Place this macro in a regular module in workbook2 and save it as a macro-enabled file. Make sure that both workbooks are open before running the macro. The macro will compare the sheets and headers in each workbook and make them all the same.

Please Login or Register  to view this content.

[sandeepgoud88]

Thanks a lot for your response i have tried using the above code and have few observations listed below.

when the sheets count is same in both the workbooks macro is running without any updates and not adding any missing columns in the sheets of srcworkbook and destination workbook.

code is directly copying the range and replacing the column headers instead can we compare the header names in the workbooks and add them if not present in any of the workbook source or destination..

if we have new column in destination sheet it is getting override by the source column headers as we are copying the range..

can you please fix this for me... that would be a great help.. let me know if you need any information..

[Mumps1]

I had a suspicion that the macro would not take care of all possible factors. I was hoping that it could be at least a starting point. Could you please attach the two actual workbooks that you are using? De-sensitize the data if necessary. Give me a few examples of how the macro is not working for you referring to specific rows, sheets and workbooks.

[daboho]

thiscode from wb2

Please Login or Register  to view this content.

[Mumps1]

Give this revised macro a try:

Please Login or Register  to view this content.

[sandeepgoud88]

Thanks for the updated macro but still i am facing some issues please find the below details

i have added few columns in workbook2 sheet ("Test") Column names are "ColumnX", "ColumnY","ColumnZ".. Once i run the code on workbook2.

Workbook2 "Test" Sheet is getting replaced with Columns of Workbook1 "Test" Sheet Columns,"ColumnE","ColumnG","ColumnH".

Expected Result:

1. Workbook2 "Test" Sheet should be updated with Columns of Workbook1 "Test" Sheet Columns,"ColumnE","ColumnG","ColumnH" and including "ColumnX", "ColumnY","ColumnZ"

2. Workbook1 "Test" Sheet Should get updated with Workbook2 "Test" Sheet Columns Columns,"ColumnE","ColumnG","ColumnH" and including "ColumnX", "ColumnY","ColumnZ"

Like this all the columns in sheets of both the workbooks should get updated..and both the workbooks should be same.

Thanks in Advance...

[Mumps1]

Let's give this a try:

Please Login or Register  to view this content.

[sandeepgoud88]

thank you very much this works really great one request can we just add a piece of code in the above macro can we even copy the data under each column in both the workbooks all the sheets and make them unique..

[Mumps1]

Are you saying that you want to copy all the data in each column from workbook1 to workbook 2 and vice versa even if the column headers already exist in both files? Please attach updated workbooks with the data included and explain in detail what you want to do.

[sandeepgoud88]

Yes, Exactly that is what am looking for. i have attached the excel workbooks. once the macro is run on workbook2 by the adding the missing column headers in both workbooks.

Workbook2 and Workbook1 should have the same data across all the sheets after running the macro. All the sheets will column called "ID" which is unique both the workbooks should have unique data based on the ID
.

Example please check the workbook1 "Test3" sheet after running the macro both the workbooks "Test3" sheet should have same data including the missing column headers and data vice versa.

[Mumps1]

I'm confused by

All the sheets will column called "ID" which is unique

In workbook1, in the ID column, there are 2 occurrences of 123, 124 and 125. To me, "unique" means there should only be one occurrence of each ID. Could you please explain further what you mean by:

All the sheets will column called "ID" which is unique both the workbooks should have unique data based on the ID

[sandeepgoud88]

let me explain you in detail..

Workbook1 as different sheets 123,124,125 can be repeated in other sheets within the workbook but not in same sheet.

so here Workbook1 and Workbook2 would be having ID Column value unique and other columns values should be compared and added based on the this value.

[Mumps1]

This is what I find confusing because in workbook1, sheet Test3, in the ID column, the ID's 123, 124 and 125 are repeated even though you say

123,124,125 can be repeated in other sheets within the workbook but not in same sheet

[sandeepgoud88]

sorry my bad.. attached the updated workbooks.

pls refer to workbook2 "Test3" sheet for more comments on the expected output. hoping this should answer your query

[Mumps1]

Test3 in workbook2 still has 2 occurrences of ID 123 in rows 7 and 12.

[sandeepgoud88]

please ignore the second occurrence it was typo error.pls refer to workbook2 "Test3" sheet for more comments on the expected output

[Mumps1]

My time is starting to get a little tight. I will work on it and get back to you as soon as I can.

[sandeepgoud88]

Sure No Problem Thanks for your help on this challenge..

[Mumps1]

Sorry for the delay. I have been trying without success to find a solution for you. Could I suggest that you start a new thread explaining your problem in detail and attaching sample workbooks. There are forum members with much more expertise than I that would most likely be able to help you out. If you do find a solution, I would appreciate it if you could send me a private message with a link to that solution so that I can have a good look at it. Thank you.