[SOLVED] auto save excel workbook on every auto update change

auto save excel workbook on every auto update change

I have an excel workbook which pulls stock data from from another source (open, close, net position etc.)

The point is, I would like to save a CSV file with every data change, for example, I have entered a position with 100 stocks, the excel has updated the information accordingly and now I would like to save it into a separate excel sheet.

The code below works just fine when I enter the information manually, but when the data is pulled via RTD (auto update the cell) the code doesn't run

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.DisplayAlerts = False

    If Not Intersect(Target, Me.Range("B33:D380")) Is Nothing Then
        ThisWorkbook.SaveCopyAs Filename:="F:\Google Drive\autosave.csv"
    End If
End Sub

Solution

Worksheet_Change() will not be triggered by a formula, like RTD, updating a cell.

For that you will need to create a Worksheet_Calculate() Sub to listen for the change.

The problem is that this worksheet function doesn't have a Range argument to test which cells are being edited...so for suggestions see this link.