Are you positive you pasted the code into Thisworkbook module?


Gord Dibben MS Excel MVP

On Tue, 15 Sep 2009 04:55:01 -0700, LiAD
wrote:

Hi,

Thanks a lot for your help.

I have tried the macro but it doesnt seem to do anything - as in nothing
changes when i close the file. If i put a stop line near the beginning of
the code it doesnt show up so i guess this means its not running it. Is
there something I need to do?

In terms of the length of data, sorry maybe i hadnt specified it but I had
though that it would just write over the top of the old data. Will this code
do this or add the same data on below the current?

"JLGWhiz" wrote:

Give this a try. Copy the code to the ThisWorkbook code module. When the
user closes the workbook, the code will run before the workbook closes and
will save the data that was copied. The only problem I see with doing it
this way is that it copies every time the workbook is closed, so the
worksheets receiving the data will build up pretty fast unless you have some
kind of system to review them and eliminate redundant data.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lr As Long, rng As Range
Dim lr2 As Long, lr3 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws3 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Sheets("Imperatifs")
Set ws3 = Sheets("Urgencies")
lr = ws1.Cells(Rows.Count, "J").End(xlUp).Row
Set rng = ws1.Range("J2:J" & lr)
For Each c In rng
If c.Value = 4 And UCase(Range("U" & c.Row).Value) = "X" Then
lr2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row
c.EntireRow.Copy ws2.Range("A" & lr2 + 1)
ElseIf c.Value = 6 And UCase(Range("U" & c.Row).Value) = "X" Then
lr2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row
c.EntireRow.Copy ws2.Range("A" & lr2 + 1)
ElseIf c.Value = 10 And _
UCase(Range("U" & c.Row).Value) = "X" Then
lr3 = ws3.Cells(Rows.Count, 2).End(xlUp).Row
c.EntireRow.Copy ws3.Range("A" & lr3 + 1)
End If
Next
ThisWorkbook.Save
ThisWorkbook.Close
End Sub


"LiAD" wrote in message
...
Hi,

Is it possible to have a copy paste macro that runs in the background, as
in
the user does not need to push a button or do anything? I guess to avoid
the
macro constantly updating when the user is in the file it would be best if
the macro ran when closing the file.

I have a table with names, dates etc in it going from col B to U. In col
J
I have the a ranking values which is either 2,4,6,8 or 10. In U I have an
X
if its an interesting item or not ( it filters whether certain actions
have
been completed etc).

I would like a macro that takes all of the items with a 10 in col J AND an
X
in col U and put them into an adjacent sheet named Urgences. Items with
either a 4 AND an X or a 6 AND an X go into a sheet named Imperatifs.

Is this sort of automatic filter/copy/paste on closing macro possible?

Thanks
LiAD