VBA, activate workbook without explicitly naming it

Question

my code has workbook A and workbook B. I copy sheets from B to A. The issue is that both will be open, so I explicitly name each workbook.

When I paste into B I activate A by its name. Is there anyway I can activate A without using the workbook name?

'Set Current Workbook as Master
Set masterWB = Application.ThisWorkbook
'Set some Workbook as the one you are copying from
Set dailyWB = Workbooks.Open(Sheets("Control Manager").Range("O2"))

'Copy the Range from dailyWB and Paste it into the MasterWB
dailyWB.Sheets("Summary").Range("A1:BJ200").Copy masterWB.Sheets("AFS Summary").Range("A1").Rows("1:1")
'formatting and paste as values
Workbooks("Workbook B").Activate
Worksheets("Summary").Select

Why are you `Activating` a Workbook at all? It's best to [avoid doing so](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Dec 10 '18 at 16:33
true, i guess instead of activating I can just use the variable I set. But does masterwb stay as "this workbook" — excelguy, Dec 10 '18 at 16:37
`ThisWorkbook` refers to the workbook that your VBA Code is stored in. So, if you have a workbook called "My File.xlsm" which has your macro, doing `Set wb = ThisWorkbook` is equivalent to `Set wb = Workbooks("My File.xlsm")`. Does that help clarify? As long as you don't then redeclare `masterwb`, it will refer to whatever workbook the running code is stored in. — BruceWayne, Dec 10 '18 at 16:43
masterWB will stay as whatever you set it to. Personally, I avoid "ThisWorkbook" like the plague. Assign your objects to variable and use the variables every time. By doing so you KNOW what the object you're working with is, since you are the one controlling the objects and not relying on Excel to do it for you. — Frank Ball, Dec 10 '18 at 16:44
@BruceWayne yes helpful. For some reason I thought it switches depending on whats being used. — excelguy, Dec 10 '18 at 16:46
@FrankBall Curious: Why do you avoid ThisWorkbook? I've never seen it go haywire on any of my projects? — jkpieterse, Dec 10 '18 at 16:46
@excelguy we're you thinking of `ActiveWorkbook`? Because using that can change depending on whatever the active book is, regardless of where the code is stored. — BruceWayne, Dec 10 '18 at 17:26
@BruceWayne , correct. So `Activeworkbook` can switch depending on what the active workbook is, but `thisworkbook` being `set` means it will stay as-is. — excelguy, Dec 10 '18 at 17:52
jkpieterse, it's an uncontrolled variable. ThisWorkbook, ActiveWorkbook and the like are dependent on processes outside of the developers control. A good developer is a control freak to the extreme. You want EVERY variable to be tightly controlled so that there is no possibility of ever ending up in a situation where what you expect isn't what you have. — Frank Ball, Dec 11 '18 at 15:32
Also, while ThisWorkbook is well controlled, using it puts you in the situation where every other workbook you are working with is reference through a variable, while ThisWorkbook isn't. Code supportability is veryu important, since none of us are immortal and if we aren't at teh job tomorrow, someone else has to be able to support our code. By using variable assignments using semi-intelligent naming conventions (ones that let the user understand what they are used for), you are helping the next guy to support your code. — Frank Ball, Dec 11 '18 at 15:32
excelguy, take a look here: https://www.excel-easy.com/vba/variables.html https://www.excel-easy.com/vba/examples/option-explicit.html https://www.excel-easy.com/vba/examples/variable-scope.html https://www.excel-easy.com/vba/examples/life-of-variables.html — Frank Ball, Dec 11 '18 at 15:35

score 0 · Answer 1 · answered Dec 10 '18 at 17:32

If you are not wanting to explicitly set your workbook, then you can always expect some room for error.

But, the following should allow you to set the alternate workbook if you only have two workbooks open - otherwise if you have more open, then this will probably not work as expected.

Dim masterWB As Workbook, dailyWB As Workbook, wb As Workbook

Set masterWB = ThisWorkbook
For Each wb In Application.Workbooks
    If wb.Name <> masterWB.Name Then 
        Set dailyWB = wb
        exit for
    End if
Next

Debug.Print dailyWB.Name

VBA, activate workbook without explicitly naming it

1 Answers1