workbook activating

[andrew299]

I have a macro that opens three workbooks when run
Two only contain data that are useful for running the macro and then can be closed down. The main workbook PRIMARY contains all the information that I wish to manipulate. The trouble is that users will want to save the manipulated info into a different file name and then come back to it. But if the program refers to PRIMARY.xls it will not work. I s there a way of reactivating the main workbook without using its file name.

Does that make sense?

Any help appreciated
Andrew299

 

[xlbo]

Activeworkbook
Thisworkbook
workbooks(1) - index number
Set the name = to a string variable for holding

Bit confused tho - if this is a workbook that needs to be opened, you will need to know it's name in the code anyway to open it initially - therefore you could use a string variable to hold the wb name

If this is the wb tha the code will be run from (ie it doesn't get opened in code), use

dim mainWB as workbook
set mainWB = activeworkbook

right at the start of your code - then just refer to mainWB Rgds
~Geoff~

 

[andrew299]

Sorry will clear it up - The workbook PRIMARY is the one the code is run from - it is not opened by the code. Two others are. The second half of your answer is what I am looking for. I am not sure if I am doing this correctly though.

I have put the line

DIM mainWB as workbook

with my other dimension variables
and the line

set mainWB = activeworkbook

just under the sub name line before the body of the proigram kicks in.
Each time I wanted to go back to PRIMARY I used the command line

Windows("PRIMARY.XLS&quot.Activate

do I simply replace this with

Windows(mainWB).activate

as this doesnt work - what am I doing wrong?

Thanks for your help

Andrew299

 

[combo]

If you write:

set mainWB = ThisWorkbook

instead of:

set mainWB = activeworkbook

mainWB will always be the workbook with code.
If you need some protection for your code try to manipulate dat by an add-in

combo

 

[xlbo]

You are setting mainWB = a Workbook Object, therefore, you should be able to use

mainWB.activate

To use the syntax you have currently, you would need to use

dim mainWB as string
mainWB = activeworkbook.name

'code
'code
'code

windows(mainWB).activate

HTH

Rgds
~Geoff~

 

[andrew299]

All I needed was the mainWB.activate thanks Geoff + combo

 

[xlbo]

Would go with combo's suggestion for using Thisworkbook as opposed to activeworkbook - then it won't matter, even if the activeworkbook is switched Rgds
~Geoff~

 

[RobBroekhuis]

But you don't need to keep a separate workbook variable - Geoff's first post contained the best suggestion: use the ThisWorkbook object. It will always refer to the workbook in which the VBA code is being executed.
Rob