Macro to compare sheetname from one workbook with another and take actions depending if there is a match or not.

Marc Bergmans 101 Reputation points


I am rather new to VBA scripting.
I am now faced with a problem for which I cannot find the solution myself.
I hope someone can help me.

I have a source workbook and a target workbook.
Each of the workbooks consists of several sheets.
For the sheets of the source workbook that also exist in the target file, a range of must be copied from the sheet of the source file to the target file.
The size of this range is always the same. The entire sheet cannot be copied, because the target workbook sheet contains formulas from a certain row and they must remain.
For sheets from the source file that do not yet exist in the target workbook, a sheet with the same name must be created in the target workbook and the range of the source workbook sheet may be copied here.
For Sheets in the target workbook whose name is a number and which do not exist in the source workbook, the range must be emptied.
The range in both the source and target workbook sheets is rows 1 to 13.

To be able to use this formula in several cases, I think it is best to enter the name of the source and target workbook in a certain cell in a sheet of the target workbook and then the contents of this cell in the VBA script assigned to specific variables.
These variables can then be used in the loops to compare the sheet names of the source and target workbook.
The VBA script would also be in the target workbook.

I can make a loop in VBA.
I think it's best to work with 2 loops for comparison.
But assigning the names of the source and target workbook to a variable and then comparing the names of the sheets of these 2 variables and then linking the correct action to it doesn't work for me.
Can someone help me with this.

Thank you in advance.

My best regards,


Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,457 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Doug Robbins - MVP 716 Reputation points


    Dim source As Workbook, target As Workbook
    Dim shtsource As Worksheet, shtTarget As Worksheet
    Dim i As Long
    Dim blnShtExists As Boolean
    Set source = Workbooks.Open("Path\name of Source workbook")
    Set target = WOrkbboks.Open("Path\name of Target Workbook")
    Set shtsource = source.Sheets("name of sheet in Source workbook")
    blnShtExists = False
    With target
        For i = 1 To .Sheets.Count
            If .Sheets(i).Name = shtsource.Name Then
                Set shtTarget = .Sheets(i)
                blnShtExists = True
                Exit For
            End If
        Next i
        If blnShtExists = False Then
            Set shtTarget = .Sheets.Add
            shtTarget.Name = shtsource.Name
        End If
    End With
    0 comments No comments