Referencing a range within a workbook

[Guest]

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("Database").Range("TeamName").Formula <> "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

 

[Tom Ogilvy]

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

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("Database").Range("TeamName").For

 

[Bob Phillips]

That should work okay, it did with my names. Is TeamName a multi-cell range,
if so that may be the problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)

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("Database").Range("TeamName").For

 

[Guest]

Bob,

No, just a single cell...

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!

Doh!

Cheers

Pete

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

Bob,

The problem was finally solved - the clipboard was being cleared because I
was doing the equivalent of Tools Options View in VBA to change the screen
display. It does this in normal Excel too, although I don't know why.
Tom Ogilvy told me about application.enableevents=false, which meant that as
I switched from workbook to workbook, I no longer invoked the screen changes,
which were called in workbook_Activate/Deactivate, and hence, the contents of
the clipboard remained intact.

Thank you for your advice - I've picked up a number of useful pointers that
I'll use in the future.

Thanks

Pete