Jayyunit
01-12-2016, 07:49 PM
Hi Guys!
I have a functioning macro that copy pastes the static values of live data from the live data sheet (Sheet), onto a separate sheet (Sheet2) every second. The code is below. For your information, Range("B2:B2195") are stock codes while Range("H2:H2195") are stock quotes.
Sub copypaste_RECENT()
Dim ab As Integer
Worksheets("Sheet").Range("B2:B2195").Copy
With Sheets("Sheet2")
.Range("B1").PasteSpecial Transpose:=True
ab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(1, 1).Value = "Time"
.Cells(ab, 1).Value = Now
Worksheets("Sheet").Range("H2:H2195").Copy
.Range("B" & ab).PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With
Application.OnTime Now + TimeSerial(0, 0, 1), "copypaste_RECENT"
End Sub
My next step is one that im having trouble with. I would like to record the difference between the stock quotes. This is a simple computation that calculates the difference between a certain cell and the cell above it and records this value onto a separate sheet (Sheet3). This computation is done for every cell in the range. This would run simultaneously to the code above so I've tried to include an additional code after End With and before the Application. The code is below.
Worksheets("Sheet").Range("B2:B2195").Copy
With Sheets("Sheet3")
.Range("B1").PasteSpecial Transpose:=True
Dim xy As Long, yz As Long
ab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
bc = .Cells(1, .Columns.Count).End(x1toleft).Column + 1
.Cells(1, 1).Value = "Time"
.Cells(ab, 1).Value = Now
xy = Worksheets("Sheet2").Cells(.Rows.Count, 1).End(x1up).Row
yz = Worksheets("Sheet2").Cells(.Rows.Count, 1).End(x1up).Row.Offset(-1, 0)
For ab = 1 To Cells(Rows.Count, 1).End(x1up).Row + 1
For bc = 1 To Cells(1, Columns.Count).End(x1toleft).Column + 1
.Cells(ab, bc).Value = xy - yz
Next ab
Next bc
End With
I'm quite new to VBA and I know this is completely wrong. I've been struggling for a while but I hope it makes some sort of sense.
Thanks in advance!
Grant
I have a functioning macro that copy pastes the static values of live data from the live data sheet (Sheet), onto a separate sheet (Sheet2) every second. The code is below. For your information, Range("B2:B2195") are stock codes while Range("H2:H2195") are stock quotes.
Sub copypaste_RECENT()
Dim ab As Integer
Worksheets("Sheet").Range("B2:B2195").Copy
With Sheets("Sheet2")
.Range("B1").PasteSpecial Transpose:=True
ab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(1, 1).Value = "Time"
.Cells(ab, 1).Value = Now
Worksheets("Sheet").Range("H2:H2195").Copy
.Range("B" & ab).PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With
Application.OnTime Now + TimeSerial(0, 0, 1), "copypaste_RECENT"
End Sub
My next step is one that im having trouble with. I would like to record the difference between the stock quotes. This is a simple computation that calculates the difference between a certain cell and the cell above it and records this value onto a separate sheet (Sheet3). This computation is done for every cell in the range. This would run simultaneously to the code above so I've tried to include an additional code after End With and before the Application. The code is below.
Worksheets("Sheet").Range("B2:B2195").Copy
With Sheets("Sheet3")
.Range("B1").PasteSpecial Transpose:=True
Dim xy As Long, yz As Long
ab = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
bc = .Cells(1, .Columns.Count).End(x1toleft).Column + 1
.Cells(1, 1).Value = "Time"
.Cells(ab, 1).Value = Now
xy = Worksheets("Sheet2").Cells(.Rows.Count, 1).End(x1up).Row
yz = Worksheets("Sheet2").Cells(.Rows.Count, 1).End(x1up).Row.Offset(-1, 0)
For ab = 1 To Cells(Rows.Count, 1).End(x1up).Row + 1
For bc = 1 To Cells(1, Columns.Count).End(x1toleft).Column + 1
.Cells(ab, bc).Value = xy - yz
Next ab
Next bc
End With
I'm quite new to VBA and I know this is completely wrong. I've been struggling for a while but I hope it makes some sort of sense.
Thanks in advance!
Grant