Does this work for you?
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("AU1: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
*How to Save a Workbook Event Macro*
1. *Copy* the macro above placing the cursor to the left of the code
box hold the *CTRL & Left Click,* then *Right Click* selected code and
*Copy.*
2. Open your Workbook and *Right Click* on any *Worksheet's Name Tab*
3. *Left Click* on *View Code* in the pop up menu.
4. Press *ALT+F11* keys to open the *Visual Basic Editor.*
5. Press *CTRL+R* keys to shift the focus to the *Project Explorer
Window*
6. Press the *Down Arrow Key* until *ThisWorkbook* is highlighted in
blue.
7. *Press* the *Enter* key to move the cursor to the *Code Window*
8. *Paste* the macro code using *CTRL+V*
9. *Save* the macro in your Workbook using *CTRL+S*
LiAD;639084 Wrote:
Hi,
I have a worksheet which uses an indirect formula to search through a
series
of files/sheets based on an address to return a value from cell AE20.
Once
the data has been copied for all the previous days I would like a
copy/paste
values to occur to replace any of the values returned by the formula.
In
AU10:AU381 there is a 1 or 0 for each row. 1 meaning that a
copy/pastevalues
can be performed (decided by if the date is < today), and zero if i
need to
keep the formulas.
Is it possible to have a copy/paste values code for only sheets 2-4
which
works when the file is closed to perform a copy paste values in cols
AG-AU if
the value in AU=1?
Thanks
LiAD
--
Simon Lloyd
Regards,
Simon Lloyd
'Microsoft Office Help' (
http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=178019
Microsoft Office Help