How to address 2 worksheets without activating each.

[SeniorTom]

I have submitted this request to mrexcel dot com/forum/excel-questions/1048844-better-way-access-2-worksheets.html (I'm too new to post a link, I trying this work around) but feel their answer was not adequate, so I came here. Sorry for the cross posting without notification.

I have 2 workbooks, Roster.xls and Response.xlms. The Response file has a list of last name that need an email address. Roster has the full name and has the email address. I have the code that allows me to find the location of the email address in Roster and insert it into Response. As it stands now, I have to activate Response to get the last name, then I have to activate the roster file to get the email address and then I have to activate the response to place the email. Man, you should see the screen flip back and forth between Workbook.

Questions:
Can I access Roster.xls without activating it or, if not, can I prevent the screen from updating the screen.

Your thoughts are appreciated.
Tom

The code is in Response.xlms:

For Count1 = FirstRow To RespLastRow
    ResponseWB.Activate
    MemberReq = Range("B" & Count1).Value
    Find_Email_in_Roster (MemberReq)
    
' Now you have the email from the Roster, set it into the Response file
    ResponseWB.Activate
    Range(emailColumn & Count1).Value = ReqEmail
Next Count1

This code gets the info from roster.xls

Sub Find_Email_in_Roster(MemberReq As String)

Dim RosCount As Long
Const RosterFirstRow As Long = 4
RosterWB.Activate

' Dim ReqEmail As Variant


RosterLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RosCount = RosterFirstRow To RosterLastRow
        If Range("B" & RosCount) Like MemberReq & "*" Then
        ReqEmail = Range("B" & RosCount).Offset(, 1).Value
                Debug.Print RosCount
                GoTo gotit
        Else
'                Debug.Print "not"
        End If
Next RosCount
gotit:

End Sub

I have created a routine that shows what is going on. Both Workbooks are open (Roster.xls and Response.xlms). When I activate a Workbooks it moves to the screen's foreground and creates a "flash" during the change. Application.ScreenUpdating does not make a difference.

For Count1 = 1 To 10
' Application.ScreenUpdating = False
ResponseWB.Activate
Application.Wait (Now + TimeValue("00:00:01"))
RosterWB.Activate
Next Count1
' Application.ScreenUpdating = True