Selected Answer
Sunil
Firstly you can't set a single (double) number to be the values of a range, as you attempted in your line:
ThisVal = Sheet2.Range("E2:F200").Value
Secondly, the arrangement of your sets of current and last/previous values (within the worksheet Report Sheet and compared to Intermediate) makes it tricky to move values and rules out the Offset/Resize method used in my recent answer how to record last value and second last value of same cell (other users should read/ try that first, please).
Attached is a revised .xlsx file (you modified from the version in my previous answer but kept the file name the same- bad!) plus a revised .xlsm file - you need to have both files up for the demo to work.
In the Intermediate sheet, I'm assuming your values have names and you can add then in column A (and the Report Sheet cells in A will reflect that via a simple formula). The macro only looks as the range upto the last item name in that column. It assumes too that the rows in the .xlsx file match those in the two sheets of the .xlsm file.
The macro to do what you want is uses two nested loops run through rows and columns of Intermediate. It's as follows, with comments and where the bits in bold need thinking about - they calculate which column the last/current/new values shift to:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name <> "Intermediate" Then Exit Sub
Dim ThisVal As Double
Dim LstRw As Long, Row As Long, Col As Long
' get last populated row of column A of Intermediate
LstRw = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
' loop through and columns of Intermediate
For Col = 5 To 6
'Loop through populated rows
For Row = 2 To LstRw
' capture each remote value in E:F
ThisVal = Sheet2.Cells(Row, Col).Value
' look at corresponding cell C:D in Report Sheet
With Sheet1
'check if value changed and modify Report Sheet
If ThisVal <> .Cells(Row, Col - 2).Value Then
'shift last value to previous
.Cells(Row, 2 * Col).Value = .Cells(Row, 2 * Col - 1).Value
'shift current value to last
.Cells(Row, 2 * Col - 1).Value = .Cells(Row, Col - 2).Value
'write new value to current
.Cells(Row, Col - 2).Value = ThisVal
End If
End With
Next Row
Next Col
End Sub
Clue: Col takes on only two values- 5 or 6 and your last / previous sets are in columns 9/10 and 11/12
Don't forget to have both new files visible on screen and change the values in the file Remote data link with changing value v0 b.xlsx.
Hope this works for you.