I think you might have an typo in this line. The range specified starts in
Column A as it is written below. The For ... Each loop will begin in Cell
A10 and cannot offset to the left. That is what is causing the error
message.


For Each MyCell In .Range("AU10:A" & .Range("AU" &
Rows.Count).End(xlUp).Row)

Maybe you only wanted Column AU in which case the line above would be
modified to:


For Each MyCell In .Range("AU10:AU" & .Range("AU" &
Rows.Count).End(xlUp).Row)



"LiAD" wrote in message
...
Hi,

I have the following code which is supposed to active on file close to
copy/paste any values in cols in cols AG-AU if the value in col AU of that
row = 1.

I have my sheets named Sheet1 etc to match this code - even if its not the
sheet names I actually want to use.

Could some-one tell me why this may not be working?

It shows an error (highlight in yellow) in these two rows
.Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value =
.Range(MyCell.Offset(0, -14).Address & ":" & MyCell.Address).Value

Thanks
LiAD

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