Hi Gregg,

Without seeing the code, it's difficult to say what may be causing your
problem. But when opening/closing all those workbooks (and having others
open at the same time), you should be especially careful to fully-qualify
your Range references. I would suggest avoiding ActiveCell, ActiveSheet,
Selection, and other unqualified references, as they will not always refer
to what you think they will (unless you actually activate a Workbook, then
Activate a Worksheet within the Workbook, which is tedious and unnecessary).
In addition, it is almost never necessary to Select or Activate anything in
Excel - you can just work directly on the objects themselves. Avoiding
selecting or activating objects will speed your code up as well.

Here's a simplified example of how I deal with opening/using multiple
Workbooks:

Sub Test()
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet

Set wb1=Workbooks.Open("C:\mysource.xls")
Set ws1=wb1.Worksheets("Source")
Set wb2=Workbooks.Open("C:\mydest.xls")
Set ws2=wb2.Worksheets("Dest")

ws2.Range("A1").Value = ws1.Range("A1").Value

Set ws2=Nothing
wb2.Close SaveChanges:=True
Set wb2=Nothing
Set ws1=Nothing
wb1.Close SaveChanges:=False
Set wb1=Nothing
End Sub

NOTE: I didn't type this into the VBE, so I may have made a syntax error or
two. But hopefully it conveys my point. If you fully-qualify everything in
this way, you shouldn't run into intermittent problems like the ones you've
been experiencing. Unless something else is wrong in the code, which
happens way too often for me. <g

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Gregg Roberts wrote:
Hi Dianne,

Based on the help file, the SpecialCells method gives the
same result whether the object it is used on is Cells or
ActiveCell. I am using this exact same statement to get
the row number of the last row of data in the "data"
workbook, so I can test to see whether I have enough rows
left in the destination workbook to my pasting, and it
works consistently.

Also, the statements being ignored are only ignored after
they have been executed many times in a loop already. As I
wrote, even the statement

Range("A1").Select

is also being ignored. The range of cells selected in the
destination workbook/sheet stays selected after this
statement is "executed." This is the level of weirdness
that is happening.

While I waited for an answer from the NG I changed my
statement to:

Selection.End(xlDown).Offset(1, 0).Select

Again, the statement works for a while, and then stops
working for no apparent reason.

There are always eight columns in the copied range, hence
no reason to test for that.

Gregg