CODEWITHSUNDEEP
vbaexcel
user3306967
user3306967

Reputation: 11

saving value of a cell in excel

I am not a programmer. I need help with excel VBA. I am trying to save the value of a cell which changes at the end of every day. I want to save the value of the cell in another cell at the end of the day before it changes to another value the next day since the cell is receiving real time data. I want to save the old data and use it for calculations. I want to do it automatically for me everyday. How do I do that using excel? Your help would be greatly appreciated.

Upvotes: 1

Views: 7298

Answers (1)

Brandon R. Gates
Brandon R. Gates

Reputation: 318

I'm going to assume that you want to save and close your file at or after 5 pm every day. If those assumptions are correct, modify the following code to suit the structure of your workbook:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sht As Worksheet
    Dim celLive As Range
    Dim celSave As Range

    If Hour(Now()) >= 17 Then
    ' ### change the below values to match your workbook structure ###
        Set sht = Sheets("MySheet")
        Set celLive = sht.Range("A1")
        Set celSave = sht.Range("B1")

        celSave.Value = celLive.Value
        ActiveWorkbook.Save
    End If
End Sub

Alternatively, you may wish to review the results of the executed code before closing the workbook by using the following code instead:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sht As Worksheet
    Dim celLive As Range
    Dim celSave As Range

    If Hour(Now()) >= 17 Then
    ' ### change the below values to match your structure ###
        Set sht = Sheets("MySheet")
        Set celLive = sht.Range("A1")
        Set celSave = sht.Range("B1")

        celSave.Value = celLive.Value
        With Application
            .EnableEvents = False
            ActiveWorkbook.Save
            .EnableEvents = True
        End With
    End If
End Sub

Put the code for either method in the Workbook module or it will not execute.

Upvotes: 1

Related Questions