Comparing Cell Across Worksheets

[jo15765]

I want to compare the Value of Cell E23 from Workbook1 to (Here's where it get's hairy) the last row with data in column H of Workbook2. If these values match..then I want to save C:\Files\Daily\ & WB.Name & "Match" & ".xls" If they do not match I want to save C:\Files\Daily\ & WB.Name & "No_Match" & ".xls"

How does VBA check values across worksheets?

[jo15765]

I am toying with this code:

Application.Workbooks("Workbook1").Sheets(1).Range("E22").Value = Application.Workbooks("Workbook2").Sheets(1).Range(LastRowColH = Range("A65536").End(xlUp).).Value

Do the experts here see any issue with this? (I am still testing it trying to customize it to my needs, and it's a lot harder than I thought it would be)

[smuzoen]

Comparing values in workbooks that are not open to my knowledge cannot be done - the workbooks need to be open. You could check to see if workbook is open and if it is not open then you could open it. The following code goes into a module in Workbook1

Sub test()
x = Sheets(1).Range("E23").Value
Windows("Workbook2.xls").Activate
y = Sheets(1).Range("H" & Cells(Rows.Count, "H").End(xlUp).Row)
If x = y Then
Windows("Workbook1.xls").Activate
 ActiveWorkbook.SaveAs Filename:="C:\Files\Daily\" & ActiveWorkbook.Name & "Match.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
Else
 ActiveWorkbook.SaveAs Filename:="C:\Files\Daily\" & ActiveWorkbook.Name & "NO_Match.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End If
End Sub

To check if the second workbook is open you would use something like

On Error Resume Next
Set wbook = Workbooks("Workbook2.xls")
If wbook Is Nothing Then
Workbooks.Open Filename:="C:\Temp\Workbook 2.xls" ' change to location of workbook2.xls
Set wbook = Nothing
On Error GoTo 0
Else
Windows("Workbook2.xls").Activate
end if

Just place this macro in a module in Workbook1 = this saves workbook2 as either match or no match - you can alter the code if it is Workbook1 you want to save.

Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[jo15765]

I am trying to implement this piece into a larger project that I am running. I can't wrap my mind around how to add this into my process, because I am using an array, to cycle through workbooks, but I don't want to leave that many workbooks open. Or add in at the end to re-open each workbook, just to compare the data. This is my current procedures:

Option Explicit
Dim wb As Excel.Workbook
Dim varBook
Dim varBooks
Public Sub Check_Data()

    Application.DisplayAlerts = False

            varBooks = Array("Mountain", "River", "Stream")

For Each varBook In varBooks
    Call Hoop1(varBook)
    Call Hoop2(varBook)
Next varBook
End Sub

Public Sub Hoop1(varBook)
        On Error Resume Next
        Set wb = Workbooks.Open(Filename:=""Z:\Daily\Richard\Daily_Data\" & WB.Name" & varBook)
            With wb
                wb.SaveAs Filename:="Z:\Daily\Richard\Daily_Data\" & varBooke
			End With
                                        
End Sub


Public Sub Hoop2(varBook)

    Set wb = Workbooks.Open(Filename:="C:\Master\Master_Spreadsheet.xls", ReadOnly:=True)
        On Error Resume Next
        Dim WS As Object
        Set WS = Nothing: Set WS = wb.Sheets(varBook)
        On Error GoTo 0
        If Not WS Is Nothing Then
            With WS
            With .UsedRange
                .Interior.ColorIndex = xlNone
            End With
            .Copy
            ActiveWorkbook.SaveAs (Filename:="Z:\Daily\Richard\Daily_Data\" & varBook)
            ActiveWorkbook.Close SaveChanges:=False
            End With
        End If
    End If

End Sub

I want to compare E22 of Hoop1 to LastCell of H in Hoop2 --- is there a way to just add this new process into my current process?

[smuzoen]

What is the purpose of the Sub Hoop1 - are you trying to rename it - your problem will be that with Set wb - there is no wb.name so this will cause an error.
What are the two file names you want to compare or are there multiple files you want to compare. As it stands your above code should fail in the first sub. I thought you just wanted to compare the value of E22 in one workbook to the value if the last cell in Column H of another workbook or do you want to check it against multiple workbooks - there is no need to have multiple workbooks open - however you will need to open a workbook to get values - perhaps I am not understanding what you are after - if you could be very specific with what you want and the file names I could help you more.
Just confirm as well you are using Excel 2000?

[jo15765]

Yes this is Excel 2000.

It is based off of multiple workbooks, for Example, I want the values in E22 Mountain Hoop1 to match the last row of H in Hoop3, and then the same for River, and the same for stream.