ThisWorkbook.Activate Question

  • Thread starter Thread starter powershorts
  • Start date Start date
P

powershorts

I was given a spreadsheet for 1st Quarter with a drop-down ment that
calls a macro that recalcs all my pivot tables and one of the first
items in that procedure is ThisWorkbook.Activate.

I copied the whole workbook and made one for 2nd Quarter.

If I open the 1st Quarter one and the menu appears, and then I open
the 2nd Quarter one... there is still only one menu (as they are
exactly the same)... if I click the menu and run the macro, which
workbook will it run on?

I envision one of three possible answers: the first one I opened, the
last one I opened, whichever workbook was active.

Help!
 
thisworkbook refers to the workbook that holds the code.

Look at the properties of the menu button and see which macro is assigned to
the button.
 
Unfortunately, both workbooks hold the exact same code. If I open
either workbook, I get the one menu. However, if I open both workbooks
at once, I only get one menu. Which workbooks menu am I seeing?
 
Add a line like:
msgbox thisworkbook.fullname

So you can see.

===
If you're duplicating code in multiple workbooks, it may be time to move the
code into a single workbook (an addin). Then run against the activesheet or
activeworkbook.

One good thing about this is that when the code needs to change--and it will or
you need a new data workbook, you don't need to update a bunch of workbooks.
 
Depends, if the code deletes the menu before re-creating it, it will be the
second. If it doesn't, the second probably fails when creating it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Depends, if the code deletes the menu before re-creating it, it will be the
second. If it doesn't, the second probably fails when creating it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
Thanks.. I looked at the code and it does indeed delete it first.
Therefore the most recently opened one would be the one.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Copy cells from one worksheet to another workseet - increment row & do again 0
commission 1
Re-driecting a pivot quarterly 1
Combine 2 columns in a dynamic range into one 7
Button looses Macro setting when running Macro 1
Still trying to average 4 worksheets and having formula problems 3
Return the current Yearly Quarter based on date 2
Rookie Question - Activating Worksheets 2

Back
Top