Store Changes in hidden sheet

  • Hello to all,

    I once had an Excel example in witch all changes made thru a user been stored on a hidden sheet.
    So later the administrator could see when and witch changes where made to the workbook.
    Now i need it i can't find this example anymore.

    I'm awere of the option of the keep track of changes function in Excel but thats not what i'm looking for.

    i'm really looking for a way to store all the changes made on a hidden sheet (saved with the workbook if closed).

    I think it has something to do with the ListChangesOnNewSheet function ore something like that.

    A search on this forum ore a Google search dident gave me the expected result.

    Any help would be great

    Thanks in advance,


  • This example will copy the usedrange of a worksheet to a hidden worksheet each time the workbook is closed. It also puts the date and user name above the copied data. To implement this, right-mouse click on the Excel icon next to the File menu and choose View Code. Then change the drop-down that says (General) to Workbook. Then in the drop-down next to it, select BeforeClose and then paste this in:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Sheets("Hidden Sheet").Visible = True
    Sheets("Hidden Sheet").Activate
    If Range("A1").Value = "" Then
    Range("A1").End(xlDown).Offset(1, 0).Select
    End If
    ActiveCell.Value = Date
    ActiveCell.Offset(0, 1).Value = Environ("Username")
    Sheets("Data Sheet").UsedRange.Copy
    ActiveCell.Offset(1, 0).Select
    Application.CutCopyMode = False
    Sheets("Hidden Sheet").Visible = False
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!