Hi Gurus,
I am facing a strange issue with excel. I am getting RTD data and wanted to store it to another sheet in same workbook but it's not working. I have to open that sheet else data is not getting copied. Strangly it is copying to other sheet but to gew sheets it's not working for me. I tried active sheet but when I reduce time to get narroe time range data, I am not able to open other sheets.
below is my code and section under "With Worksheets("NFD")" and With Worksheets("BNFD") are not working.
for sheet NFData and BNFData ut's working fine.
Sub TickHandler(ByVal hwnd&, ByVal lngMsg&, ByVal lngTimerId&, ByVal lngTime&)
On Error Resume Next
'NFD CODE
With Worksheets("NFD")
col = getLastUsedCol(3, "NFD")
.Cells(1, col) = Left(TimeValue(Now), 5)
.Cells(2, col) = Sheets("NF").Cells(1, 1).Value
.Cells(17, col) = Sheets("NF").Cells(1, 1).Value
.Cells(16, col) = Left(TimeValue(Now), 5)
.Range(Cells(3, col), Cells(15, col)) = Sheets("NF").Range("R3:R15").Value
.Range(Cells(18, col), Cells(30, col)) = Sheets("NF").Range("V3:V15").Value
End With
'end nfd
'BNF CODE
With Worksheets("BNFD")
col = getLastUsedCol(3, "BNFD")
.Cells(1, col) = Left(TimeValue(Now), 5)
.Cells(2, col) = Sheets("BNF").Cells(1, 1).Value
.Cells(17, col) = Sheets("BNF").Cells(1, 1).Value
.Cells(16, col) = Left(TimeValue(Now), 5)
.Range(Cells(3, col), Cells(15, col)) = Sheets("BNF").Range("R3:R15").Value
.Range(Cells(18, col), Cells(30, col)) = Sheets("BNF").Range("V3:V15").Value
End With
'end Bnfd
row = FindingLastRow("NFData")
Sheets("NFData").Cells(row, 1) = TimeValue(Now())
'Time NF OI Volume Price BS TV
'NF
Sheets("NFData").Cells(row, 2) = Sheets("NF").Cells(1, 1).Value
'OI
Sheets("NFData").Cells(row, 3) = (Sheets("NF").Cells(16, 10).Value - Sheets("NF").Cells(16, 4).Value)
'Vol
Sheets("NFData").Cells(row, 4) = (Sheets("NF").Cells(16, 12).Value - Sheets("NF").Cells(16, 2).Value)
'Price
Sheets("NFData").Cells(row, 5) = (Sheets("NF").Cells(16, 8).Value - Sheets("NF").Cells(16, 6).Value)
'BS
Sheets("NFData").Cells(row, 6) = Sheets("NF").Range("V16").Value
'TV
Sheets("NFData").Cells(row, 7) = (Sheets("NF").Range("w16").Value - Sheets("NF").Range("x16").Value)
'fut oi
Sheets("NFData").Cells(row, 8) = Sheets("NF").Cells(1, 6).Value
'BNF DATA
row = FindingLastRow("BNFData")
Sheets("BNFData").Cells(row, 1) = TimeValue(Now())
'Time NF OI Volume Price BS TV
'NF
Sheets("BNFData").Cells(row, 2) = Sheets("BNF").Cells(1, 1).Value
'OI
Sheets("BNFData").Cells(row, 3) = (Sheets("BNF").Cells(16, 10).Value - Sheets("BNF").Cells(16, 4).Value)
'Vol
Sheets("BNFData").Cells(row, 4) = (Sheets("BNF").Cells(16, 12).Value - Sheets("BNF").Cells(16, 2).Value)
'Price
Sheets("BNFData").Cells(row, 5) = (Sheets("BNF").Cells(16, 8).Value - Sheets("BNF").Cells(16, 6).Value)
'BS
Sheets("BNFData").Cells(row, 6) = Sheets("BNF").Range("V16").Value
'TV
Sheets("BNFData").Cells(row, 7) = (Sheets("BNF").Range("w16").Value - Sheets("BNF").Range("x16").Value)
Sheets("bNFData").Cells(row, 8) = Sheets("bNF").Cells(1, 6).Value
End Sub
Thanks in advance
Sabu
I am facing a strange issue with excel. I am getting RTD data and wanted to store it to another sheet in same workbook but it's not working. I have to open that sheet else data is not getting copied. Strangly it is copying to other sheet but to gew sheets it's not working for me. I tried active sheet but when I reduce time to get narroe time range data, I am not able to open other sheets.
below is my code and section under "With Worksheets("NFD")" and With Worksheets("BNFD") are not working.
for sheet NFData and BNFData ut's working fine.
Sub TickHandler(ByVal hwnd&, ByVal lngMsg&, ByVal lngTimerId&, ByVal lngTime&)
On Error Resume Next
'NFD CODE
With Worksheets("NFD")
col = getLastUsedCol(3, "NFD")
.Cells(1, col) = Left(TimeValue(Now), 5)
.Cells(2, col) = Sheets("NF").Cells(1, 1).Value
.Cells(17, col) = Sheets("NF").Cells(1, 1).Value
.Cells(16, col) = Left(TimeValue(Now), 5)
.Range(Cells(3, col), Cells(15, col)) = Sheets("NF").Range("R3:R15").Value
.Range(Cells(18, col), Cells(30, col)) = Sheets("NF").Range("V3:V15").Value
End With
'end nfd
'BNF CODE
With Worksheets("BNFD")
col = getLastUsedCol(3, "BNFD")
.Cells(1, col) = Left(TimeValue(Now), 5)
.Cells(2, col) = Sheets("BNF").Cells(1, 1).Value
.Cells(17, col) = Sheets("BNF").Cells(1, 1).Value
.Cells(16, col) = Left(TimeValue(Now), 5)
.Range(Cells(3, col), Cells(15, col)) = Sheets("BNF").Range("R3:R15").Value
.Range(Cells(18, col), Cells(30, col)) = Sheets("BNF").Range("V3:V15").Value
End With
'end Bnfd
row = FindingLastRow("NFData")
Sheets("NFData").Cells(row, 1) = TimeValue(Now())
'Time NF OI Volume Price BS TV
'NF
Sheets("NFData").Cells(row, 2) = Sheets("NF").Cells(1, 1).Value
'OI
Sheets("NFData").Cells(row, 3) = (Sheets("NF").Cells(16, 10).Value - Sheets("NF").Cells(16, 4).Value)
'Vol
Sheets("NFData").Cells(row, 4) = (Sheets("NF").Cells(16, 12).Value - Sheets("NF").Cells(16, 2).Value)
'Price
Sheets("NFData").Cells(row, 5) = (Sheets("NF").Cells(16, 8).Value - Sheets("NF").Cells(16, 6).Value)
'BS
Sheets("NFData").Cells(row, 6) = Sheets("NF").Range("V16").Value
'TV
Sheets("NFData").Cells(row, 7) = (Sheets("NF").Range("w16").Value - Sheets("NF").Range("x16").Value)
'fut oi
Sheets("NFData").Cells(row, 8) = Sheets("NF").Cells(1, 6).Value
'BNF DATA
row = FindingLastRow("BNFData")
Sheets("BNFData").Cells(row, 1) = TimeValue(Now())
'Time NF OI Volume Price BS TV
'NF
Sheets("BNFData").Cells(row, 2) = Sheets("BNF").Cells(1, 1).Value
'OI
Sheets("BNFData").Cells(row, 3) = (Sheets("BNF").Cells(16, 10).Value - Sheets("BNF").Cells(16, 4).Value)
'Vol
Sheets("BNFData").Cells(row, 4) = (Sheets("BNF").Cells(16, 12).Value - Sheets("BNF").Cells(16, 2).Value)
'Price
Sheets("BNFData").Cells(row, 5) = (Sheets("BNF").Cells(16, 8).Value - Sheets("BNF").Cells(16, 6).Value)
'BS
Sheets("BNFData").Cells(row, 6) = Sheets("BNF").Range("V16").Value
'TV
Sheets("BNFData").Cells(row, 7) = (Sheets("BNF").Range("w16").Value - Sheets("BNF").Range("x16").Value)
Sheets("bNFData").Cells(row, 8) = Sheets("bNF").Cells(1, 6).Value
End Sub
Thanks in advance
Sabu