Excel macro help

[Woden]

So I have a sub (pasted below) I have started working on. It works just as I would expect when the target sheet is open, but not when it isn't. Obviously, half the point of this is not having to open it. Any ideas what I did wrong?

It's the start of a bigger macro that will copy the data found in a cell on the same row of that sheet, into another sheet that we will be using directly.

Sub ScanTrailer()
    ' Select the contingency data
    With Workbooks("Shaw Contingency Report.xls").Sheets("BusinessContingency_Report")
        .Activate
        .Range("C2").Select
    End With

    ' Variables
    Dim Found As Boolean
    Found = False
    Dim x As String
    x = "WHSE\DES\24"
    
    Do Until IsEmpty(ActiveCell)
        ' Check active row for yard path
        If ActiveCell.Value = x Then
            Found = True
            Exit Do
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
    ' Return found data
    If Found = True Then
        MsgBox ("Value found in cell " & ActiveCell.Address)
    Else
        MsgBox ("Value not found")
    End If
End Sub

 

[balanceballs]

You need to change the way you access the sheet. This should help:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=279

 

[Woden]

You need to change the way you access the sheet. This should help:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=279

Thanks, I'll try that out.

As a work around I set the macro to open the file, get the stuff it needs without updating the screen, then close the window.

It still opens. But doesn't take over the screen.