I have made a sheet which contains a column which is used to insert
installation dates, however these dates change from time to time due to
failed installations or rescheduled installation dates.

Is there a way which I can use to automatically track these changes in a
separate sheet?
E.g. Automatically copy the row to a new sheet, to keep track of changes?

Thanks in advance
--
SiliconAlleyDude

You could use worksheet event code to copy the entire row to another sheet
whenever a date is entered or a change is made to a date in that particular
column.

Assumes you have a Sheet1 for entry and a Sheet2 for tracking.

Also assumes dates are entered/edited in Column A of Sheet1

The copying is accumulative in Sheet2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo stoppit
Application.EnableEvents = False
For Each rng In vRngInput
If Target.Value < "" Then
Target.EntireRow.Copy Destination:= _
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next
stoppit:
Application.EnableEvents = True
End Sub

This is worksheet code.

Right-click on the Sheet1 tab and "View Code".

Paste the above into that module.


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 23:39:02 -0800, "SiliconAlleyDude"
wrote:

I have made a sheet which contains a column which is used to insert
installation dates, however these dates change from time to time due to
failed installations or rescheduled installation dates.

Is there a way which I can use to automatically track these changes in a
separate sheet?
E.g. Automatically copy the row to a new sheet, to keep track of changes?

Thanks in advance

Gord,

I tried this, for some reason it is not working.
Sheet 1: Installation 1
A B C
1 StoreID Store Name Date Success
2 00001 Chicago 04/18 Yes
3 00002 New York 04/18 Yes
4
5

Sheet 2: Installation 2
A B C
1 StoreID Store Name Date Success
2 00001 Chicago 05/18 Yes
3 00002 New York 05/18 Yes
4
5

If the installation failed, the success value will be No.
At this point I want automatically copy the entire row with the original
values to a separate sheet name "Change Control"

I know I can copy and paste special this in the change control sheet, but
this is what I want to automate.

Thanks in advance,

S

"Gord Dibben" wrote:

You could use worksheet event code to copy the entire row to another sheet
whenever a date is entered or a change is made to a date in that particular
column.

Assumes you have a Sheet1 for entry and a Sheet2 for tracking.

Also assumes dates are entered/edited in Column A of Sheet1

The copying is accumulative in Sheet2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo stoppit
Application.EnableEvents = False
For Each rng In vRngInput
If Target.Value < "" Then
Target.EntireRow.Copy Destination:= _
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next
stoppit:
Application.EnableEvents = True
End Sub

This is worksheet code.

Right-click on the Sheet1 tab and "View Code".

Paste the above into that module.


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 23:39:02 -0800, "SiliconAlleyDude"
wrote:

I have made a sheet which contains a column which is used to insert
installation dates, however these dates change from time to time due to
failed installations or rescheduled installation dates.

Is there a way which I can use to automatically track these changes in a
separate sheet?
E.g. Automatically copy the row to a new sheet, to keep track of changes?

Thanks in advance

I don't doubt it is not working<g

One of us misunderstood the original request.

You said you had one sheet, now you say you have two sheets.

You said you wanted the rows copied when the date was changed, now you want a
row copied when "Success" is yes or no.

I'm not sure how you want to deal with "installation1" and "installation2"
from two separate sheets and copy to a third sheet.

Are sheet1 and sheet2 correlated in some manner or are they standalone?

Would you want rows from both sheets copied to the "Change Control" sheet?


Gord

On Fri, 25 Mar 2005 00:31:06 -0800, "SiliconAlleyDude"
wrote:

Gord,

I tried this, for some reason it is not working.
Sheet 1: Installation 1
A B C
1 StoreID Store Name Date Success
2 00001 Chicago 04/18 Yes
3 00002 New York 04/18 Yes
4
5

Sheet 2: Installation 2
A B C
1 StoreID Store Name Date Success
2 00001 Chicago 05/18 Yes
3 00002 New York 05/18 Yes
4
5

If the installation failed, the success value will be No.
At this point I want automatically copy the entire row with the original
values to a separate sheet name "Change Control"

I know I can copy and paste special this in the change control sheet, but
this is what I want to automate.

Thanks in advance,

S

"Gord Dibben" wrote:

You could use worksheet event code to copy the entire row to another sheet
whenever a date is entered or a change is made to a date in that particular
column.

Assumes you have a Sheet1 for entry and a Sheet2 for tracking.

Also assumes dates are entered/edited in Column A of Sheet1

The copying is accumulative in Sheet2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo stoppit
Application.EnableEvents = False
For Each rng In vRngInput
If Target.Value < "" Then
Target.EntireRow.Copy Destination:= _
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next
stoppit:
Application.EnableEvents = True
End Sub

This is worksheet code.

Right-click on the Sheet1 tab and "View Code".

Paste the above into that module.


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 23:39:02 -0800, "SiliconAlleyDude"
wrote:

I have made a sheet which contains a column which is used to insert
installation dates, however these dates change from time to time due to
failed installations or rescheduled installation dates.

Is there a way which I can use to automatically track these changes in a
separate sheet?
E.g. Automatically copy the row to a new sheet, to keep track of changes?

Thanks in advance

Gord,

Sorry for the inconvenience. All installation sheets are stand-alone in the
workbook.
If success is "No" then this row needs to be copied into the "Change Contol"
sheet.

Thanks in advance

S.

"Gord Dibben" wrote:

I don't doubt it is not working<g

One of us misunderstood the original request.

You said you had one sheet, now you say you have two sheets.

You said you wanted the rows copied when the date was changed, now you want a
row copied when "Success" is yes or no.

I'm not sure how you want to deal with "installation1" and "installation2"
from two separate sheets and copy to a third sheet.

Are sheet1 and sheet2 correlated in some manner or are they standalone?

Would you want rows from both sheets copied to the "Change Control" sheet?


Gord

On Fri, 25 Mar 2005 00:31:06 -0800, "SiliconAlleyDude"
wrote:

Gord,

I tried this, for some reason it is not working.
Sheet 1: Installation 1
A B C
1 StoreID Store Name Date Success
2 00001 Chicago 04/18 Yes
3 00002 New York 04/18 Yes
4
5

Sheet 2: Installation 2
A B C
1 StoreID Store Name Date Success
2 00001 Chicago 05/18 Yes
3 00002 New York 05/18 Yes
4
5

If the installation failed, the success value will be No.
At this point I want automatically copy the entire row with the original
values to a separate sheet name "Change Control"

I know I can copy and paste special this in the change control sheet, but
this is what I want to automate.

Thanks in advance,

S

"Gord Dibben" wrote:

You could use worksheet event code to copy the entire row to another sheet
whenever a date is entered or a change is made to a date in that particular
column.

Assumes you have a Sheet1 for entry and a Sheet2 for tracking.

Also assumes dates are entered/edited in Column A of Sheet1

The copying is accumulative in Sheet2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo stoppit
Application.EnableEvents = False
For Each rng In vRngInput
If Target.Value < "" Then
Target.EntireRow.Copy Destination:= _
Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next
stoppit:
Application.EnableEvents = True
End Sub

This is worksheet code.

Right-click on the Sheet1 tab and "View Code".

Paste the above into that module.


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 23:39:02 -0800, "SiliconAlleyDude"
wrote:

I have made a sheet which contains a column which is used to insert
installation dates, however these dates change from time to time due to
failed installations or rescheduled installation dates.

Is there a way which I can use to automatically track these changes in a
separate sheet?
E.g. Automatically copy the row to a new sheet, to keep track of changes?

Thanks in advance