same worksheets in two workbooks vba code

[excel_puppy]

hello,
i have two workbooks (names change, not consistent) with similar sheet names. For example, book1 has sheet1, sheet2 and sheet3 while book2 has sheet1, sheet3, sheet4 and sheet5. each sheet contains data. what i'm looking for is a way to have the same sheets on each workbook. so book1 would have the original sheet1,sheet2 and sheet3 (no changes to the data), and now sheet4 and sheet5 are added (both would be blank, just need the sheet names added). for book2, the original sheet1,sheet3,sheet4 and sheet5 remain untouched while a blank sheet2 is added. so in the end, both workbooks have sheet1,sheet2,sheet3,sheet4 and sheet5 in the same order. the newly added sheets will remain blank in their respective workbook. the reason for this question is so that we can compare the two workbooks and search for differences but for this to work, the sheets need to be identical and in same order. any advice would be greatly appreciated!

 

[Saurabhj]

Hi, if the sheets are only 4-5 why we are not adding manually?

We can use VBA to make workbooks identical but would like to know the purpose of automating.

 

[excel_puppy]

Hi, if the sheets are only 4-5 why we are not adding manually?

We can use VBA to make workbooks identical but would like to know the purpose of automating.

Oh no, it may be hundreds of sheets. I’m just giving a small example. The purpose is to compare two workbooks and look for differences. For that to work our workbooks must have same sheets and in same order. We compare today’s workbook with yesterday’s looking for differences. If a new sheet(s) is added or deleted this throws us off. Hope that makes sense.

 

[excel_puppy]

might help if i add what i've got so far. it causes a "that name is already taken. try a different one" which i like because i don't want it added if it already exists on the other WB. it also adds an ambiguous sheet # to the workbook.
Sub Look_Thru_WB_WS()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsName As String
wsName = ""
For Each wb In Application.Workbooks
If wb.Name <> "PERSONAL.XLSB" Then
For Each ws In wb.Worksheets
If ws.Name <> wsName Then
sheets.Add.Name = ws.Name
End If
Next ws
End If
Next wb
End Sub