compare two cells in different sheets

[srr797]

Hi,

I am hoping someone can help me with what seems to be a pretty basic code. I have two worksheets in the same workbook : sheet1 and sheet2. I need code that will compare cell B1 in each sheet and delete the sheet with the smaller value. Also, if the sheets have the same number in cell B1, then delete sheet2.

Thanks in advance!

 

[machopicho]

Try:

Sub test()
Application.DisplayAlerts = False
Select Case Sheets("Sheet1").Range("B1").Value
Case Is < Sheets("Sheet2").Range("B1").Value
Sheets("Sheet1").Delete
Case Is > Sheets("Sheet2").Range("B1").Value
Sheets("Sheet2").Delete
Case Is = Sheets("Sheet2").Range("B1").Value
Sheets("Sheet2").Delete
End Select
Application.DisplayAlerts = True
End Sub

 

[srr797]

Thanks for your help. The code works for what I had asked. However, I have now run into a problem; that it will show an error if either sheet1 or sheet2 do not exist. Is there a way to prompt this code only to run when the two sheets exist?

Thanks again!

 

[sage_bob]

You could just add an 'on error' part to the code.

Sub test()

    On Error GoTo ErrTrap

    Application.DisplayAlerts = False

    Select Case Sheets("Sheet1").Range("B1").Value
        Case Is < Sheets("Sheet2").Range("B1").Value
            Sheets("Sheet1").Delete
        Case Is > Sheets("Sheet2").Range("B1").Value
            Sheets("Sheet2").Delete
        Case Is = Sheets("Sheet2").Range("B1").Value
            Sheets("Sheet2").Delete
    End Select

    Application.DisplayAlerts = True

    Exit Sub

ErrTrap:
    ' You could put some code here which will only
    ' run if one of the sheets are not there.
End Sub

Hope that helps.

 

[machopicho]

Sub test()
On Error GoTo Control
Application.DisplayAlerts = False
Select Case Sheets("Sheet1").Range("B1").Value
Case Is < Sheets("Sheet2").Range("B1").Value
Sheets("Sheet1").Delete
Case Is > Sheets("Sheet2").Range("B1").Value
Sheets("Sheet2").Delete
Case Is = Sheets("Sheet2").Range("B1").Value
Sheets("Sheet2").Delete
End Select
Application.DisplayAlerts = True
Control:
MsgBox ("One sheet or value doesn't exist!")
End Sub

 

[srr797]

great works perfectly!

 

[Peter_SSs]

For what it's worth, here's another way to approach the issues. One thing to note is that you shouldn't have to test for 3 different conditions.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Sheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet, ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("Sheet1"): <SPAN style="color:#00007F">Set</SPAN> ws2 = Sheets("Sheet2")<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">If</SPAN> ws1 <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> ws2 <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "At least one sheet doesn't exist"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> ws1.Range("B1").Value < ws2.Range("B1").Value <SPAN style="color:#00007F">Then</SPAN><br>            ws1.Delete<br>        <SPAN style="color:#00007F">Else</SPAN><br>            ws2.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>