how to calculate workbook. If every row is dynamically chage by rtd server.

[ganu learner]

My workbook is linked with rtd data server . In this workbook first row is Title and from 2nd to 15 row is dynamically change from rtd server. I want to calculate my workbook for every row . Presently I creat 14 new sheets for according by row and run two kind of vba scripts for every row. for even rows (2,4,6,8,10,12,14) I run vba script 1 and for odd rows (3,5,7,9,11,13,15) I run vba script 2.

in this way my workbook size is too much and my workbook calculation if performing too slow after some time . Please find another way to do calculating of workbook . thanks



vba script 1.


Private Sub Worksheet_Calculate()
Dim capturerow As Long, currow As Long, col As String
Dim SayThis As String
On Error GoTo handerror
Application.EnableEvents = False
If Time > TimeValue("09:14:55") Then
capturerow = 2
currow = Cells(Rows.Count, 2).End(xlUp).Row
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 4) = Cells(capturerow, 4)
Cells(currow + 1, 5) = Cells(capturerow, 5)
Cells(currow + 1, 10) = Cells(capturerow, 10)
ivr = Range("A4").Value
ivf = Range("A3").Value
Cells(currow, col) = Cells(currow, "D") - Cells(currow - 3, "D")
Range("H3").Value = Cells(currow - 3, 8)
Range("I3").Value = Cells(currow - 3, 9)
Range("H2").Value = WorksheetFunction.Sum(Range("H2:H3"))
Range("I2").Value = WorksheetFunction.Sum(Range("I2:I3"))
Range("E2").Value = WorksheetFunction.Sum(Range("I2")) - WorksheetFunction.Sum(Range("H2"))
Range("T2").Value = Cells(capturerow, 10)
handerror:
Application.EnableEvents = True
End If
End Sub

vba sciprt 2

Private Sub Worksheet_Calculate()
Dim capturerow As Long, currow As Long, col As String
Dim SayThis As String
On Error GoTo handerror
Application.EnableEvents = False
If Time > TimeValue("09:14:55") Then
capturerow = 2
currow = Cells(Rows.Count, 2).End(xlUp).Row
Cells(currow + 1, 2) = Cells(capturerow, 2)
Cells(currow + 1, 4) = Cells(capturerow, 4)
Cells(currow + 1, 5) = Cells(capturerow, 5)
Cells(currow + 1, 10) = Cells(capturerow, 10)
ivr = Range("A4").Value - 1
ivf = Range("A3").Value
Cells(currow, col) = Cells(currow, "D") - Cells(currow - 3, "D")
Range("H3").Value = Cells(currow - 3, 8)
Range("I3").Value = Cells(currow - 3, 9)
Range("H2").Value = WorksheetFunction.Sum(Range("H2:H3"))
Range("I2").Value = WorksheetFunction.Sum(Range("I2:I3"))
Range("E2").Value = WorksheetFunction.Sum(Range("I2")) - WorksheetFunction.Sum(Range("H2"))
handerror:
Application.EnableEvents = True
End If
End Sub