Using a Macro to create a retention sheet

[leviathan86]

E.g in this workbook I have two sheets, Source and Retention Sheet

My problem is that the source is constantly updated and I need to maintain a historical record of what the value of these cells were, almost like track changes.

So the macro should
1) When executed Copy =Source!A1:U8 and paste values into the retention sheet that would time, date and name stamp the person that run the macro.
2) I would like it so the macro could be run multiple times and retain the data on the next available row.
3) A prompt box that would allow the user to replace the previous entry if required.

I could easily create a new sheet and paste values into there using a macro, however, that could just build too many sheets.

[JieJenn]

You mean something like

Please Login or Register  to view this content.

?

[leviathan86]

Thanks, by the look of it yes. However I see two changes

1) Prompt Box: Ideally it would have a drop down menu with the option to replace data from a particular date (since every record of data would be included) I apologize for not specifying this in my original post. OR the option to paste in data at the next available row. All data would take on the exact same number of cells. So for drop down dates, It would be like a2+10. then a2+10+10 etc

2) if that makes things to complicated then i would just like the macro to paste values at the next available row. ideally i would like the drop down option.

[leviathan86]

Any ideas? im fine with the simplest option as well.

[leviathan86]

How do I add it to my current code

Sub Retention()

Range("'View Summary'!D10:BN34").Copy
Sheets("Retention").Range("a" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues


End Sub

You mean something like

Please Login or Register  to view this content.

?