PDA

View Full Version : Comparing 2 Workbooks AND Their Worksheets



simora
10-08-2016, 06:56 PM
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
10-08-2016, 08:54 PM
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
10-08-2016, 10:48 PM
mikerickson:


(http://www.vbaexpress.com/forum/member.php?10706-mikerickson)Having a problem organizing the code so that each worksheet gets compared in the other workbook.
Didn't understand your question.

simora
10-08-2016, 10:53 PM
mikerickson: (http://www.vbaexpress.com/forum/member.php?10706-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
10-08-2016, 11:05 PM
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
10-08-2016, 11:23 PM
mikerickson: (http://www.vbaexpress.com/forum/member.php?10706-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
10-08-2016, 11:37 PM
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
10-08-2016, 11:54 PM
mikerickson: (http://www.vbaexpress.com/forum/member.php?10706-mikerickson)
Unfortunately I'm getting an error at the start of the code. For Each oneSheet In Workbook [ sub or function not defined ]

simora
10-09-2016, 12:05 AM
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
10-09-2016, 08:22 AM
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
10-09-2016, 08:41 AM
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
10-09-2016, 03:04 PM
mikerickson: (http://www.vbaexpress.com/forum/member.php?10706-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
10-09-2016, 03:07 PM
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
10-09-2016, 03:15 PM
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
10-09-2016, 03:26 PM
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.