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



"Bob Phillips" wrote:

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)


"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