Comparing 2 workbooks for missing sheet in excel vba

Question

I had a requirement which is in excel macro. Here is the scenario :

I have 2 workbooks, Say A and B

Workbook "A" contains, sheet 1, sheet2 and sheet3, Workbook "B" contains sheet4

Now i need to compare Workbook "B" with Workbook A. If a sheet exist in Workbook "A" which is not in Workbook "B" (Here Sheet1, Sheet2, Sheet3) then i need to add these 3 sheets to Workbook "B"

So finally, B workbook should contain : Sheet 1, sheet 2, sheet 3 and sheet 4.

Tried below code but its not working.

Set act = ThisWorkbook
path = Sheet1.TextBox1.Text
Set owb = Workbooks.Open(Filename:=path)
For Each ws In ThisWorkbook.Worksheets
    a = ws.Name
    For Each ws1 In owb.Worksheets
       If ws1.Name = a Then
          MsgBox "Found"
       Else
          Set wsnew = owb.Sheets.Add
          wsnew.Name = a
       End If
     Next ws1
Next ws

sure, it throwing error as "Name already exists". In fact it creates sheet but loop not ending. — user2829218
At which line does the error is raised? And what sheet name are you trying to set when the error is raised? — Ahmad

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-12-05T09:53:03

Rather than check each worksheet you could try and set a reference to it - if the reference is set then the sheet exists, if it throws an error then it doesn't exist.

I'd use a separate function to return TRUE/FALSE on the sheet existing - one of the few times that I'd use On Error Resume Next to ignore any errors.

Public Sub CopySheets()

    Dim wrkBk As Workbook
    Dim wrkSht As Worksheet
    Dim wrkShtNew As Worksheet

    Set wrkBk = Workbooks("Book2")

    For Each wrkSht In ThisWorkbook.Worksheets
        If Not WorkSheetExists(wrkSht.Name, wrkBk) Then
            Set wrkShtNew = wrkBk.Worksheets.Add
            wrkShtNew.Name = wrkSht.Name
        Else
            MsgBox wrkSht.Name & " exists.", vbOKOnly + vbInformation
        End If
    Next wrkSht

End Sub

Public Function WorkSheetExists(SheetName As String, Optional wrkBk As Workbook) As Boolean
    Dim wrkSht As Worksheet

    If wrkBk Is Nothing Then
        Set wrkBk = ThisWorkbook
    End If

    On Error Resume Next
        Set wrkSht = wrkBk.Worksheets(SheetName)
        WorkSheetExists = (Err.Number = 0)
        Set wrkSht = Nothing
    On Error GoTo 0

End Function

Comparing 2 workbooks for missing sheet in excel vba

1 Answers