Hello-
I have a workbook with multiple spreadsheets that is updated regularly. Each time I make changes to the workbook, I have to go to the first spreadsheet and update the version number and date in two separate cells. Is there a formula that I can use that will update these two cells each time a change is made in the workbook?
Any help would be greatly appreciated!
Do you want any manual change to increase the version number by 1 and update the date to the current date? If so, open the VB Editor (Alt+f11), locate your workbook in the Project Explorer window and double-click the ThisWorkbook object, then paste this in (and adjust the referenced cells as required):
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo reset_events
' turn events off to prevent recursion
Application.EnableEvents = False
' assumes first sheet has the version number and date
With Sheets(1)
' version number - change range as necessary
.Range("A2").Value2 = .Range("A2").Value2 + 1
' current date
.Range("B2").Value = Date
End With
reset_events:
Application.EnableEvents = True
End Sub
Please note: this will be triggered by every change you make, so you might be better off with a manual routine that you run whenever you want the version and date updated.
Thank you so much!
