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.)
Hi,
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
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
Sunil
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 SubNote 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:
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 SubNote 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.