thisworkbook.activate

[grt]

hi,
I'm trying to avoid the chance that my VBA project will execute procedures
for sheets it shouldn't touch. I understood that using
'ThisWorkbook.Activate' in my code will work in such a way that the code
will always solely run in the workbook within which the procedure runs.

Though, while testing this I found out that with CMD-` I can get a different
window in front of me whereby the commands do work. Meaning, the front-
most window is changed according to the procedure that starts with
something like:
thisworkbook.activate
.....
Anybody the same experience? Solutions?

 

[JE McGimpsey]

I'm trying to avoid the chance that my VBA project will execute procedures
for sheets it shouldn't touch. I understood that using
'ThisWorkbook.Activate' in my code will work in such a way that the code
will always solely run in the workbook within which the procedure runs.

Though, while testing this I found out that with CMD-` I can get a different
window in front of me whereby the commands do work. Meaning, the front-
most window is changed according to the procedure that starts with
something like:
thisworkbook.activate
.....
Anybody the same experience? Solutions?

In my experience, the solution is to avoid using activations or
selections. Instead, qualify your references. For instance, instead of

ThisWorkbook.Activate
Sheets("Sheet1").Activate
Range("A1:J10").Select
Selection.Copy
Sheets("Sheet2").Activate
Range("B30").Select
Activesheet.Paste

reference the ranges directly:

ThisWorkbook.Sheets("Sheet1").Range("A1:J10").Copy Destination:= _
ThisWorkbook.Sheets("Sheet2").Range("B30")

or, the With...End With shortcut is a bit more efficient.

With ThisWorkbook
.Sheets("Sheet1").Range("A1:J10").Copy Destination:= _
.Sheets("Sheet2").Range("B30")
End With

Post back if you need specific examples.