Excel Macro: Selecting items from another worksheet withoutactivating it

[whitethomas12]

I have a question. I have several macros that I have written that
requires them to look through (copy, select, validate, etc) other
worksheets for data.

Is there away to go through these worksheets without the end-user
actually seeing the macro run through them

For Example: Lets say we have something like this

Dim cname as string
Dim searchName as string

cname = Range("A1").Value
Sheets("Sheet2").Select
Range("A1").Select
searchName = Activecell.value
Do While Activecell.value <> ""
If searchName = cname then
msgbox "Sheet2 has this name" & searchName
End If
Loop

Granted this is a simple example, but it does demostrated that a user
will see Excel switch between sheets

Is there away around this??

 

[Neil Eves]

try
Application.screenupdating=false
your code
Application.screenupdating=true

Regards
Neil

 

[JLGWhiz]

Most of the time, you can avoid the flicker and flash by eliminating the
select and activate method of writing the code. Here is an example of the
sample you posted converted to a more direct method.

cname = ActiveSheet.Range("A1").Value
Set c = Sheets("Sheet2").Cells(Find, What:=Range("A1").Value)
If Not c Is Nothing Then
If c.Value = cname Then
MsgBox "Sheet2 has this name " & c.Value
End If
End If
End Sub

It uses the Find method rather than stepping through each cell in a loop.
It is much faster and does not have to select or activate sheet 2. You just
have to make sure that you qualify your command lines with the correct sheet
object so it knows where to look and where to execute. Otherwise, it thinks
all commands are for the active sheet.

Just to be sure, it never hurts to include the command lines that Neil
suggested also.