Referring to a workbook whose name changes

[richand1]

People

I have a macro stored as a personal macro that will run in a spreadsheet which will have a different name everytime.

My question is:

Is there a way, without cutting the data into a new spreadsheet and saving that with a common name (that way just seems too messy), that I can revert back to the original spreadsheet after selecting another open workbook?

Something like Windows("<Workbookname>").Activate, but I'd have to declare the active workbook or something. Can't think of the code to do it...

 

[SkipVought]

Hi,

You have not described your problem very well.

Before you close the WORKBOOK, you can set an object to that workbook, regardless of the name, and refer to that object in your code.

But once the workbook is SAVED with a different name and CLOSED, you have to know that workbook name to open it at some other time.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[PHV]

Perhaps off-topic but when writing general purpose macro take care of the difference between ActiveWorkbook and ThisWorkbook.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[StuckInTheMiddle]

Yeah good point PHV, that one got me when I first started out those many moons ago

 

[AnotherHiggins]

Do you mean something like

myWB = activeworkbook.name
'...
'open another workbook
'...
Windows(myWB).Activate

So myWB will be the name of whatever workbook you started with?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

John, why not simply this ?
myWB.Activate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit submit too fast ...
In fact it's rarely necessary to use either Activate or Select methods ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[StuckInTheMiddle]

It's necessary if you are SELECTING

 

[PHV]

Selecting for doing what ?

 

[StuckInTheMiddle]

If you are trying to SELECT a certain cell in a certain worksheet in a specific workbook, that workbook and sheet need to be the active or you get errors.

 

[PHV]

But why would you select a cell ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

->why not [simplify] this
Fair enough. I was just looking at the OP, in which richand1 typed "Something like Windows([red]"<Workbookname>"[/red]).Activate" and it seemed to me that no one had clearly answered the question for him.

Why activate a sheet? It is easier for novice VBA programmers (IMHO) because it allows you to step through code and SEE what everything is doing.

Why activate a cell? I, for one, am very particular about what you see when a user first opens a sheet. I want the correct area of the correct sheet to be visible.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[StuckInTheMiddle]

PHV,

I create long reports and/or input screens in XL for various users who require that the XL cursor appear in various places by default on entry to a particular (worksheet). This is done by Selecting cells, and completely falls over if you dont have the correct workbook and worksheet SELECTED first.

A,

 

[richand1]

Whoa. You guys really know what you're talking about!

I'm a novice really, but anotherhiggins was close enough for me to fill in the rest.

Thanks a lot chaps.