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

Question

Hello,

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,

Hello,

Answer 1

Try

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