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:

Please Login or Register  to view this content.

This code gets the info from roster.xls

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

[Norie]

Tom

This can definitely be done but I'm a bit confused as to how the code you posted fits together.

[6StringJazzer]

In general you never have to activate a workbook, worksheet, or cell just to operate on it.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Zer0Cool]

Write you code explicitly instead of implicitly.

Explicit:

Please Login or Register  to view this content.

Implicit:

Please Login or Register  to view this content.

The implicit code assumes active workbook and active sheet, explicit is told which sheet and workbook.

You can ease the amount of code written using explicit terms by declaring variables representing your workbook(s), worksheet(s), range(s), etc. You can then also combine them with With statements to make using properties and methods of those objects easier.

For ex:

Please Login or Register  to view this content.

So in the above example I declare 2 variables for 2 separate workbooks, then declare a variable for 2 worksheets and 2 ranges. I set the workbook variables, set a sheet variable to a sheet within each workbook and set a range within each. I can then utilize those objects without needing fully explicit code for each time I use it. I also included a With statement, which implies the parent object, .copy is really ws1.Range("C1:C10").copy which is really Workbooks("Workbook1").Worksheets(1).Range("C1:C10").copy.

Declaring variables also significantly speeds up your macros and makes them easier to follow and easier to update.

You should almost never need to use .activate or .select. Workbooks, Worksheets, Ranges, etc can all be acted upon without activating them or selecting them and doing so can drastically slow down your code and lead to other issues (especially when using implicit code as you need to keep track in your head whats implied).

[SeniorTom]

Folks, thanks for your feedback. I must be missing something. If I don't activate the Roster file and I step through, I get Error 1004 ... Application or Object define error at rosterlastRow. If I activate RosterWB all works except ' Debug.Print RosterWB.ActiveSheet

Please Login or Register  to view this content.

I will be out of town ‘til Tuesday, so I won’t have a chance to test or reply. I’m saying this so you don’t think, I’m ignoring the feedback.

[6StringJazzer]

You can't debug.print a worksheet object. You probably want

Please Login or Register  to view this content.

The problem on the last line is that Rows must also be qualified, because is a property of RosterWB.Sheets(1). Since it is within a With block, you need only add a dot:

Please Login or Register  to view this content.

[SeniorTom]

6SringJazzer,

Thank you so very much. I spent a lot of time and frustration and all it was, was a "dot". The qualifier did the trick. one dot and all is well. The .name, I figured out on my own but did not know why. Thanks for that too.

Tom

[AliGW]

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

[SeniorTom]

Hello Ali,

Sorry for the cross posting without notification. I have edited the initial request, but could not put the link in as directed. I got a notification that I was too new to post links. If I did not handle the correction correctly, please let me know.

Tom

[bakerman2]

Deleted , just saw post#1 was only edited today.