Better way to access 2 worksheets

[SeniorTom]

I have 2 spreadsheets, 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 Workbooks.

The code 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

The code is 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

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

 

[JoeMo]

If both workbooks are open you should be able to avoid activating altogether. If all you want to do is suppress the flicker try adding this line at the start of your first subroutine:

Application.ScreenUpdating = False

And this line before the End Sub:

Application.ScreenUpdating = True

 

[SeniorTom]

Application.ScreenUpdating
That did not work, I think it is for the spreadsheet not the screen.

 

[JoeMo]

No, it's for the screen. Did you set it equal to false? Where exactly did you place it in your subroutine?

 

[SeniorTom]

Application.ScreenUpdating = False
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
Application.ScreenUpdating = True

 

[SeniorTom]

I have created a routine that shows what is going on. Both worksheets are open (Roster.xls and Response.xlms). When I activate a worksheet 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

 

[JoeMo]

Then I would focus on rewriting your code so it runs from the workbook it is in and doesn't need to activate the other workbook.

 

[SeniorTom]

I got the answer. All I had to do was create a "with" to the sheet and add 2 dots to RosterLastRow
With Workbooks(RosterFileName).Sheets("Sheet1")

RosterLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...eets-without-activating-each.html#post4869615

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[SeniorTom]

Sorry for the cross posting on https://www.excelforum.com/excel-p...ss-2-worksheets-without-activating-each.html without notifying this Forum. I was unaware of the rules and will conform from now on.