I can't think of any situation where opening another workbook would affect
the variables in an existing workbook unless a reset command or perhaps a
stop command is issued. Just because one workbook is a copy of the original
workbook should have no effect. Variables, even public variables are local
to the workbook.


--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Tom,

The REAL problem is, in my Workbook_Deactivate macro, I call a routine
which
changes some screen options, resets the Excel menu bar etc (so that if any
clever user hits Ctrl+F6, the Deactivate macro runs and they get normal,
uncusomised Excel), which in turn clears the clipboard (see my other
posting
that you replied to).

When I consolidate, I open other workbooks, all of which contain identical
macro sheets and variable declarations, because when they're created,
using
save As within VBA, they're based on the original workbook. As all these
variable declarations are identical in each workbook, as soon as my
consolidate macro opens another workbook, all my variables, along with
their
contents are redeclared, and thus emptied.

Because of the clipboard emptying effect, I wanted to say "If I'm
consolidating, don't run the screen reset in Workbook_Deactivate"), but as
I
can't store ANYTHING without it being reset, I'm b******d, so to speak.

I think I'll have to think the whole thing out again, but, once again,
thanks for your interest and advice!

Regards

Pete



"Tom Ogilvy" wrote:

If you set your variable with

varname = Thisworkbook.Name

then it isn't going to be visible to any workbook other than the
workbook
running the code and it will refer to the workbook running the code.
In
otherwords, if I set a variable in Book1, it won't be visible to code in
book2 or code in book3. So I suspect you are getting an error because
you
are using a local version of your variable and it is empty.

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Tom,

The problem is, all my workbooks are based on the same workbook, using
SaveAs, so they all contain the same variable declarations - so, as
soon
as I
open the workbook to be consolidated, all the contents of my variables
get
set back to empty!

I need to always know the name of the first workbook I started with,
so I
can check in the "Teamcell" cell to see if a consolidation is taking
place
(the "Consolidating..." message is pasted there when the consolidate
macro
begins), and this, NOT run the code (as shown in my first post)

I know I should somehow create my Save As files so that they don't
have
any
macro sheets (and thus don't reset the values in the variables in the
consolidating workbook every time they open) but I don't know how to
do
this.

I think I probably need an add in that is independent of any workbook
being
used in the consolidation process.

Thanks

Pete



"Tom Ogilvy" wrote:

Why chase your tail:

if thisworkbook.Names("TeamName").RefersToRange.Text < _
"Consolidating - please wait..." then

if TeamName is a sheet level name, then use "DataBase!TeamName"
rather than just "TeamName"

Using the Text property insures a string to string comparison.

doing
ConsolidatorWorkBookName = ThisWorkbook.Name

then using the variable

is like saying "Peter, what's your first name?" You always have
access
to
the ThisWorkbook object. So you can always get the name.

--
Regards,
Tom Ogilvy



"Peter Rooney" wrote in
message
...
Good afternoon, all!

I have a workbook that uses a VBA routine to consolidate a number
of
other
workbooks into itself using the rough logic: open workbook to be
consolidated
- copy data - return to consolidating workbook - paste data -
return
to
consolidated workbook - close - repeat as necessary for multiple
files.

I want to be able to run some code ONLY if the value of a
particular
cell
in
the Consolidator workbook doesn't equal a certain value.

The problem is I can't find the correct syntax to reference the
range.
I have trapped the name of the consolidating workbook to a
variable
"Consolidator WorkBookName" with ConsolidatorWorkBookName =
ThisWorkbook.Name
and want to say something like:

If



Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For
mula < "Consolidating - please wait..." Then
ScreenReset
End If

but this gives me a "Type Mismatch"

Can anyone tell me where I'm going wrong?

I've tried to create a concatenated string that includes double
quotes
at
the beginning and the end of the workbook name, and stripping the
".xls"
from
the end, but to no avail.

Thanks in advance

Pete