Macro to compare sheetname from one workbook with another and take actions depending

[marcopone]

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,

[georgiboy]

Hi Marcopone,

I have created the below to work outside of the source and destination workbooks as an example for you, i have defined the source and destination workbooks at the top of the code. There are probably more efficient ways of doing this but the below may get you and the other contributors started.

Sub test()    
    Dim sCol As New Collection, dCol As New Collection
    Dim wbS As Workbook, wbD As Workbook, ws As Worksheet
    Dim MakeNew As Boolean, ToClear As Boolean, a, b
    
    Set wbS = Workbooks.Open("C:\Users\clarkg\Desktop\test\Source.xlsx") ' source wb
    Set wbD = Workbooks.Open("C:\Users\clarkg\Desktop\test\Destination.xlsx") ' destination wb
    
    For Each ws In wbS.Worksheets ' collection of source wb sheet names
        sCol.Add ws.Name, CStr(ws.Name)
    Next ws
    For Each ws In wbD.Worksheets ' collection of destination wb sheet names
        dCol.Add ws.Name, CStr(ws.Name)
    Next ws
    
    For Each a In sCol ' loop to move data and create sheet if needed
        For Each b In dCol
            If a = b Then
                wbS.Sheets(a).Rows("1:13").Copy
                wbD.Sheets(a).Rows(1).PasteSpecial xlAll
                Application.CutCopyMode = False
                MakeNew = False
                Exit For
            Else
                MakeNew = True
            End If
        Next b
        If MakeNew Then
            With wbD
                .Sheets.Add , .Sheets(.Sheets.Count)
                .ActiveSheet.Name = a
                wbS.Sheets(a).Rows("1:13").Copy
                .Sheets(a).Rows(1).PasteSpecial xlAll
            End With
            Application.CutCopyMode = False
        End If
    Next a
    
    For Each b In dCol ' loop to check for numeric sheet name in wbD and clear the rows if not in wbS
        For Each a In sCol
            If a <> b And IsNumeric(b) Then
                ToClear = True
            Else
                ToClear = False
            End If
        Next a
        If ToClear Then
            wbD.Sheets(b).Rows("1:13").ClearContents
        End If
    Next b
    
    wbD.Close True ' close source workbook
    wbS.Close False ' close destination workbook
End Sub

Hope this helps

[SamT]

comparing the names of the sheets of these 2 variables and then linking the correct action to it doesn't work for me.

I call this a "True when False" Statement. The If... Then is only entered when the names don't match

On Error resume next
ShtCreated = False
If SourceSheet.Name <> Target.Sheets(SourceSheet.Name).Name Then
   Target.Sheets.Add Name = SourceSheet.Name
   ShtCreated = True
End If
Err = 0

After this, there will always be a Sheet named after the Source sheet in the Target book, the Boolean Flag will tell you if it was newly created.

[snb]

Why don't you

- post the 2 workbooks here
- illustrate in the workbooks what you are after
- post the code you created this far
- post this question in a forum in your native language