Is there a way to save historical entries

[dragonmouse]

I have a large spreadsheet that gets updated daily. In a nutshell I have several preventative maintenance actions that need to be preformed. Weekly, Monthly and yearly for example. I would like to save the last 3 dates in a summarized spreadsheet on another worksheet. when I change the date a macro or similar action copies that date to the next work sheet:

A B C D
Replace Filter 01-JUN-2108 07-Jun-2018 01-AUG-2018


When I change the date in column B to 08-JUN-2018 I'd like "replace filter to appear on another worksheet to say 01-JUN-2018"

Is this possible?

 

[Logit]

.
Presuming your data is located on Sheet 1 like so :

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]ACTION[/td][td]Date 1[/td][td]Date 2[/td][td]Date 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Replace Filter[/td][td]

1-Jun-08​

[/td][td]

7-Jun-18​

[/td][td]

1-Aug-18​

[/td][/tr]
[/table]


You could use the following on Sheet 2 :

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]ACTION[/td][td]Date 1[/td][td]Date 2[/td][td]Date 3[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Replace Filter[/td][td]

1-Jun-08​

[/td][td]

7-Jun-18​

[/td][td]

1-Aug-18​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td][/td][td]=Sheet1!B2[/td][td]=Sheet1!C2[/td][td]=Sheet1!D2[/td][/tr]
[/table]

 

[Logit]

.
I got to looking at your post again ... I am not certain what I provided is exactly what you were requesting.

On the second sheet where the entries from Sheet1 will be copied to ... are you wanting Sheet2 to maintain all copied entries forever, without being overwritten
by future entries on Sheet1 ?

The formulas I provided for Sheet2 will mirror what is entered on Sheet1, but each time you make a change in B1 on Sheet1, the data on Sheet2 B1 will change as well.

How long do you want the copied data on Sheet2 to last/be archived ?

???

 

[dragonmouse]

Close but not quite.

Date 1 will be replaced with a new date. I'd like to preserve the OLD date that got replace. Date 2 is a different frequency from date 1, so it will have it's own dates to memorize when replaced.

 

[Logit]

.
See if this works for your needs :

Option Explicit


Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Set ws = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws2.Range("A:A")) + 1
    
    ws2.Cells(newRow, 1).Value = ws.Range("A3").Value
    ws2.Cells(newRow, 2).Value = ws.Range("B3").Value
    ws2.Cells(newRow, 3).Value = ws.Range("C3").Value
    ws2.Cells(newRow, 4).Value = ws.Range("D3").Value
    
    ws.Cells(3, 1).Value = "-"
    ws.Cells(3, 2).Value = Me.TextBox1.Value
    ws.Cells(3, 3).Value = Me.TextBox2.Value
    ws.Cells(3, 4).Value = Me.TextBox3.Value
       
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/LT3iV6TXAxNZ2jJ14smjYzwS1xCxxsgZrA87Z3zEnZq