What does the error message say? A range of cells cannot have a value.
Only a single cell can have a value. You will get a "Type Mismatch" error
with this code. What do you want to accomplish with these 2 lines of code?
HTH Otto

"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