Sorry forgot the code

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).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


"LiAD" wrote:

OK. Now the code I have is below and it seems to be running - its just not
doing exactly what I'd hoped.

If I watch two of the columns that have data in them, (0.0 or a value), when
the code runs it replaces the data with #REF. Any cells that do not have a
number in these two columns also show #REF, but this is normal as no data is
yet available for these days (the date is greater than yesterdays date).
Somehow there is an issue with the copy/paste values. For info the data is
copied from the source file using INDIRECT(). The same happens on all three
sheets.

Two other small issues
- it is very slow to complete this operation. Is there any way of speeding
it up?
- if I want to change the sheet names from Sheet1 to a,b,c what is the best
way of doing this? (I possible i'd rather that the code referred to sheet
2-4 etc but the sheet name can be anything and be changeable without having
to alter the code - its sheet 2-4 that are of interest for now).

In the future I may have about 15 sheets, hence the need for speed and
changeable sheet names.

Would you have any ideas on these three points?
Thanks for your help
LiAD


"JLGWhiz" wrote:

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


.