Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Record the result in excel sheet from remote file



I am looking for the recording the result in excel sheet.

For example:

When the result in cell update with "TRUE" (formula based cell) than the data to record in next sheet in newest to oldest date order (attached sheets for ready reference).

Please help for solution.

Thank you



Hi Sunil

Is this another case where the cells are linked to data is updated remotely? (If so, you should say so in your question and identify which cells are affected).

Did you attach the right file? (I see no sign of an attempt at coding a solution).
John_Ru (rep: 6142) Apr 25, '23 at 3:42 am
Yes, the file is linked to remote file.
In Sheet1 from "Record the results.xlsx", whenever there is a change in column D (Remarks), of comment to "TRUE" (formula based) than the said row to record in Recording sheet in Newest to Oldest time (attached sheet for ready reference).
Trust you are able to understand.
Please help for solution.

SunilA (rep: 58) Apr 25, '23 at 5:29 am

Thanks for the extra information but you didn't correct your question as I asked you to. (Check the Forum rules and I think they guide you like that - questions should be complete and not rely on me or someone else remembering what you're been trying to do in the past. Questions should be "self-contained" in my opinion).

Also you're not new to VBA so we expect you to try to make a solution, then ask questions when you get stuck.
John_Ru (rep: 6142) Apr 25, '23 at 5:50 am
I am trying below coding, but unable to get the result.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

If Sh.Name <> "Recording" Then Exit Sub

Dim ThisVal As Range
' capture remote value
ThisVal = Sheet2.Range("D2:D200").Value

' look as Range D2:D200 in Report Sheet
With Sheet1.Range("D2:D200")
    'check if value changed and modify Report Sheet
        If ThisVal = "True" Then
            .Offset(0, 1).Value
            ' write new value and time(below)
            .Value = ThisVal
            .Offset(0, 0).Value = Now
        End If
End With

End Sub
Please guide.

SunilA (rep: 58) Apr 25, '23 at 10:18 am
Sunil. You know that code doesn't work- I said why recently and showed you an alternative. I don't have time to look at this right now 
John_Ru (rep: 6142) Apr 25, '23 at 2:33 pm
Hi Sunil

Did you try the solution in my Answer?
John_Ru (rep: 6142) May 4, '23 at 6:37 am
Hi Sunil. 

Did you see my answer?
John_Ru (rep: 6142) May 5, '23 at 5:30 am
Add to Discussion


Selected Answer


In the first revised (.xlsm) file, I've defined two variables at the Workbook level (so their values are retained), followed by an event macro to captures the values in column D of the "Remote values" sheet when the file is launched:

Dim LastVals As Variant, LstRw As Long

Private Sub Workbook_Open()
    ' capture depth of sheet (col A) and current values (col D)
    With Sheet1
        LstRw = .Range("A" & Rows.Count).End(xlUp).Row
        LastVals = .Range("D1:D" & LstRw).Value
    End With
End Sub

Note that it populates an array LastVals with values but converts your "TRUE" entries to Boolean True (so I need to use CStr to convert it back to a string in the macro below)

If the second (.xlsx) file is open and a cell in column B is changed to "Missing" (so column D becomes "TRUE") then the calculate event macro below is triggered (with comments for guidance), The two key (bold) lines do the following:

  1. tests if a row value was "" but became "True", if so it:
    • inserts a row 2 near the top oif sheet "Recording"
    • copies A:D to that row
    • adds the time to column E
  2. records the new values in D in the array LastVals for the next triggger of the macro.
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Dim Rw As Long

' check a change occured on the first sheet
If Sh.Name <> "Remote values" Then Exit Sub

' prevent retrigger and reverse the change
Application.EnableEvents = False

With Sheet1
    LstRw = .Range("A" & Rows.Count).End(xlUp).Row
    ' compare to find change
    For Rw = 2 To LstRw
        ' see if a value changed to (Boolean) True
        If LastVals(Rw, 1) = "" And CStr(.Cells(Rw, 4).Value) = "True" Then
            'insert row
            Sheet2.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
            ' add data and time to row 2
            Sheet2.Cells(2, 1).Resize(1, 4).Value = .Cells(Rw, 1).Resize(1, 4).Value
            Sheet2.Cells(2, 5).Value = Time
        End If
    Next Rw

    'save changed values
    LastVals = .Range("D1:D" & LstRw).Value
    Application.EnableEvents = True

End With

End Sub

Note that it relies on you having values in column A:C of worksheet "Remote values". It's okay to use a formula (like you have in column D of that sheet).

Hope this fixes things for you.



Did you try that, Sunil? 
John_Ru (rep: 6142) May 9, '23 at 9:30 am
Yes John, Its working. Thank you for your solution.
I must say, You have very good knowledge in excel.
Till date, you have solved all my query. Very much Appreciated.
Thank you John for your support like always.
SunilA (rep: 58) May 12, '23 at 1:31 am
Glad that worked for you. Your queries / approaches intrigue me and luckily I like to solve puzzles. Thanks for selecting my Answer. Sunil. 
John_Ru (rep: 6142) May 12, '23 at 2:38 am
I wish, i could get someone like you for my python queries.
SunilA (rep: 58) May 15, '23 at 1:46 am
Sunil. I don't know Python but suggest you try StackOverflow, e.g. Questions tagged [python]
John_Ru (rep: 6142) May 15, '23 at 6:12 am
Thank you John
SunilA (rep: 58) May 15, '23 at 2:04 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login