same worksheets in two workbooks vba code

excel_puppy

New Member
Joined
Feb 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 5, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,157
Messages
5,629,027
Members
416,359
Latest member
Juena

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top