Reopening error message

[msnook]

I have almost exactly the same coding in two different spreadsheets. In one workbook it works without a hitch on the other I get an error message (which I think I should get but dont want). Can anyone tell me why there is a difference between the two sheets and what I could do to get rid of the error message?

Thanks in advance.
--------------------------------------
Example 1 works fine:
Sub findaname2()

y = 7
For X = 6 To 20
Windows("Book1.xls").Activate
z = (Worksheets("Data").Range("A" & X))

If z <> "" Then

Workbooks.Open ("C:\Documents and Settings\snook_mi\Desktop\Part B.xls")

For a = 2 To 1086

With Worksheets("Operator Activity Report")

If ((.Range("C" & a)) = z) Then

.Range("B" & a, "N" & a).Copy
Workbooks("Book1.xls").Worksheets("Data").Range("C" & ).PasteSpecial xlPasteAll
y = y + 1
End If

End With
Next a
End If
Next X

Workbooks("Part B.xls").Close SaveChanges:=True


--------------------------------------
Example 2 with error message:

(replenish.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen replenish.xls yes/no)

y = 7
For X = 2 To 20
Windows("Tim's Tracker.xls").Activate
z = (Worksheets("Data").Range("A" & X))

If z <> "" Then

Workbooks.Open ("C:\Documents and Settings\snook_mi\Desktop\replenish.xls")

For a = 2 To 2000

With Worksheets("replenish")

If ((.Range("E" & a)) = z) Then

.Range("D" & a, "G" & a).Copy
Workbooks("Tim's Tracker.xls").Worksheets("Data").Range("C" & ).PasteSpecial xlPasteAll
y = y + 1
End If

End With
Next a
End If

Next X
Workbooks("replenish.xls").Close SaveChanges:=True

 

[xlbo]

why are you re-opening the workbook if it is already open ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[msnook]

I have tried to move the open statement and i get error messages

 

[xlbo]

If it is (and always will be) already open, just remove the line

or alternatively :

With Workbooks("Replenish").Worksheets("replenish")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question