Comparing 2 Workbooks AND Their Worksheets

[simora]

I'm stumped here.

I have 2 workbooks.Book1.xls & Book2.xls

I need to look at all the sheets in Book2 and see if there's an Equivalent sheet in Book1.

If there is,( The sheets exist in both workbooks ) and Cell C3 OR C8 in Book1 is more than 0.00 but Cell C3 OR C8 in Book2 is 0.00 OR NOTHING , then copy that sheet from Book1 to Book2.( Replace the sheet in Book2 with that from the Book1 copy )

If on the other hand Cell C3 OR C8 in both worksheets contain data > 1.00, BUT the values are NOT THE SAME, then ADD ( SUM) the values of all cells from Book1 to Book2 for all cells in Book2.Range ("D2:AH31") For that worksheet.

So Book1 remains Unmodified, but Book2 worksheets are updated with some Book1 data.

[mikerickson]

In this situation

Book1 C3 = .5
Book1 C8 = 1.5

Book2 C3 = 0
Book2 C8 = 1.2

Do you copy or add?

According to the OP:

"Cell C3 or C8 in Book 1 is more than 0.00" (true) + "Cell C3 or C8 in Book2 is 0.00 or nothing" (true), therefore copy
"Cell C3 or C8 in both workbooks contain data > 1.00" (true) "the values are not the same" (true), therefore add

[simora]

mikerickson:


Having a problem organizing the code so that each worksheet gets compared in the other workbook.
Didn't understand your question.

[simora]

mikerickson:

RE: I need to look at all the sheets in Book2 and see if there's an Equivalent sheet in Book1.

Which Sheet were you looking at ?

[mikerickson]

I wasn't looking at a particular sheet, but was inquiring about the conditions for copying vs. adding.

If you want to look through a workbook and see if each of its sheet's has a similarly named sheet in another workbook, you could use code like

Sub Test()
    Dim oneSheet as worksheet

    For Each oneSheet in Workbook("WorkBook1.xlsm").Worksheets
        If SheetNamedExistsIn (Workbooks("WorkBook2.xlsm"), oneSheet.Name) Then
            MsgBox "A sheet named " & oneSheet.Name & " is in Workbook2"
        Else
            MsgBox "no sheet named " & oneSheet.Name & " is in Workbook2"
        End If
    Next oneSheet
End Sub


Function SheetNamedExistsIn(ByVal wb as Workbook, byVal sheetName as String) as Boolean
    On Error Resume Next
    SheetNamedExistsIn = (LCase(wb.Sheets(sheetName).Name) = LCase(sheetName))
    On Error Goto 0
End Function

[simora]

mikerickson:

"Cell C3 or C8 in both workbooks contain data > 1.00" (true) "the values are not the same" (true), therefore
( SUM) the values for all cells in Range ("D2:AH31") For that worksheet.
So Sheet2 from Book1 gets copied to Book2 replacing it.
I just realized that there was no Sheets 6 or 7 in what was uploaded.
I did have some cells in both sheets where the cell values in Book1. Sheet 6 will need to be added to Book2. Sheet6

[mikerickson]

The example that I posted meets both of the stated conditions, what do you want to happen when that occurs.

What do you want to happen when neither of the stated conditions applies, e.g. all the cells in question have negative values?

[simora]

mikerickson:
Unfortunately I'm getting an error at the start of the code. For Each oneSheet In Workbook [ sub or function not defined ]

[simora]

RE: "Cell C3 or C8 in both workbooks contain data > 1.00" (true) "the values are not the same" (true), therefore add [ YES ]
In these cases, we do add the values of the cells in Range (C2:AI31) .
The results of this is new values for Book2 which is now the SUM of the OLD & NEW values.

RE: What do you want to happen when neither of the stated conditions applies,?
Then we do NOTHING. We just move on to comparing the next worksheet.

[mikerickson]

Regarding
"[if] Cell C3 OR C8 in both worksheets contain data > 1.00, BUT the values are NOT THE SAME"

What about
Book1 C3 = 1.5, Book1 C8 = 0.5
Book2 C3 = 0.4, Book 2 C8 = 1.5

Does that situation meet the condition?

How about
Book1 C3 = 1.5, Book1 C8 = 0.5
Book2 C3 = 0.5, Book2 C8 = 1.5

or

Book1 C3 = 1.5, Book1 C8 = 0.5
Book2 C3 = "x", Book2 C8 = 1.5

or
Book1 C3 = 1.5, Book1 C8 = 0.5
Book2 C3 = 1.5, Book2 C8 = 1.8

[mikerickson]

I'm still unclear about when to add and when to copy, but this is what I have so far.
Open the attached workbook, open Book2 and then run the sub Test

' in a normal code module in Workbook 1 (the one that doesn't change)

Sub test()
    Dim Book1 As Workbook, Book2 As Workbook
    Dim Sh1 As Worksheet, Sh2 As Worksheet
    Dim Data1 As Variant, Data2 As Variant
    Dim i As Long, j As Long
    
    Dim CopyFlag As Boolean, AddFlag As Boolean
    
    Set Book1 = ThisWorkbook
    
    For Each Book2 In Application.Workbooks
        If Book2.Windows(1).Visible And Book2.Name <> Book1.Name Then
            If MsgBox("Update " & Book2.Name & "?", vbYesNo) = vbYes Then Exit For
        End If
    Next Book2
    
    If Book2 Is Nothing Then
        MsgBox "goodbye"
    Else
        Application.DisplayAlerts = False
        For Each Sh1 In Book1.Worksheets
            If SheetNamedExists(Book2, Sh1.Name) Then
                Set Sh2 = Book2.Worksheets(Sh1.Name)
                CopyFlag = (0 < Val(Sh1.Range("C3").Value)) Or (0 < Val(Sh1.Range("C8").Value))
                With Sh2
                    If (IsNumeric(.Range("C3")) And (Val(.Range("C3").Value) = 0)) Or (.Range("C3") = vbNullString) Then
                        CopyFlag = CopyFlag And True
                    Else
                        CopyFlag = CopyFlag _
                            And (IsNumeric(.Range("C8")) And (Val(.Range("C8").Value) = 0)) Or (.Range("C8") = vbNullString)
                    End If
                End With
                
                With Sh1
                    AddFlag = (1 < Val(.Range("C3").Value)) Or (1 < Val(.Range("C8").Value))
                End With
                With Sh2
                    AddFlag = AddFlag And ((1 < Val(.Range("C3").Value)) Or (1 < Val(.Range("C8").Value)))
                End With
                AddFlag = AddFlag And Not ((Sh1.Range("C3").Value = Sh2.Range("C3").Value) And (Sh1.Range("C8").Value = Sh2.Range("C8").Value))
                 
                If AddFlag Then
                    MsgBox "add" & Sh1.Name
                    Data1 = Sh1.Range("D2:AH31").Value
                    With Sh1.Range("D2:AH31")
                        Data2 = .Value
                        For i = 1 To .Rows.Count
                            For j = 1 To .Columns.Count
                                If IsNumeric(Data1(i, j)) And IsNumeric(Data2(i, j)) Then
                                    Data2(i, j) = Data1(i, j) + Data2(i, j)
                                End If
                            Next j
                        Next i
                        .Value = Data2
                    End With
                ElseIf CopyFlag Then
                    MsgBox "copy " & Sh1.Name
                    Sh1.Copy before:=Sh2
                    ActiveSheet.Name = Chr(5)
                    Sh2.Delete
                    Book2.Sheets(Chr(5)).Name = Sh1.Name
                Else
                    MsgBox "do nothing with " & Sh1.Name
                End If
            End If
        Next Sh1
    End If
    Application.DisplayAlerts = True
End Sub

Function SheetNamedExists(wb As Workbook, SheetName As String) As Boolean
    On Error Resume Next
    SheetNamedExists = (LCase(wb.Worksheets(SheetName).Name) = LCase(SheetName))
    On Error GoTo 0
End Function

[simora]

mikerickson:


Thanks for your assistance on this. You pointed me in the right direction.
Now the procedures have changed and each person updates their own sheet, rather than having 1 person do them all,
so I've just implemented a simple sheet swap. Made life a lot easier.

I'm Having a problem capturing the cell value of the first worksheet to add to the same cell address on the same worksheet name in the other workbook?
How do I code it so that the cell value changes as the code goes through the range.
This is the formula & Code that I'm using when running the code from Book2.xls.

For Each cell In Range("D2:AH31")
cell.Formula = " =[Book1.xls]Sheet1!$D$2+Sheet1!$D$2"

SO I'm trying to add the value of Workbooks("Book1.xls").Sheets("Sheet1").Range("D2") + Workbooks("Book2.xls").Sheets("Sheet1").Range("D2")
And have that value in Range("D2") in ("Book2.xls").Sheets("Sheet1").Range("D2")

[mikerickson]

That formula would put a circlular reference in cells of [Book2.xls]Sheet1! , so you will have to put values into the cells (as my code does) rather than formulas.

[simora]

I inserted some numbers into the sheet when I tried your code. It did not sum the values.
I was looking to use something along those lines but couldn't get it to SUM the values.

[simora]

BTW : Your code DID COPY the sheets correctly. THANKS.
I just realized that it would have to do the SUM of the cells as a separate operation before any copying could take place under the original process.
As it stands now, I'm only interested in summing those cells in place.