Hi,

Thanks for your help.

If I try the the two dots ..Range as suggested it says syntax error. If I
delete the second dot and then try to run the code, (i.e. the code below) it
says Application or object defined error. In col AG I have just text values
(months of the year), could this affect it?

LiAD

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long, MyCell As Range
For i = 1 To 3 Step 1
With Sheets("Sheet" & i)
For Each MyCell In .Range("AU10:AU" & .Range("AU" & Rows.Count).End(xlUp).Row)
If MyCell = 1 Then
..Range(MyCell.Offset(0, -14)).Value = .Range(MyCell.Offset(0, -14)).Value
End If
Next MyCell
End With
Next i
ThisWorkbook.Save
End Sub

"joel" wrote:


You are copying the from the dame address to the same address is that
what you really wnat?


code should look like this


..Range(MyCell.Offset(0, -14)).Value = ..Range(MyCell.Offset(0,
-14)).Value


You could be getting errors for these reasons

1) MyCell column - 14 is 0 or less
2) MyCel is not set defined
3) The workbook that Mycell was set to is closed (2nd parent)
4) The sheet that Mycell is set to is set to is not a worksheet
(1st parent)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=178854

Microsoft Office Help

.