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