Range copy issue

[sudhakars]

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

 

[sudhakars]

You are not defining the sheets with the Cells again

Sheets("NFD").Range(Sheets("NFD").Cells(3, col), Sheets("NFD").Cells(15, col)) = Sheets("NF").Range("R3:R15").Value
Sheets("NFD").Range(Sheets("NFD").Cells(18, col), Sheets("NFD").Cells(30, col)) = Sheets("NF").Range("V3:V15").Value

or

Range(Sheets("NFD").Cells(3, col), Sheets("NFD").Cells(15, col)) = Sheets("NF").Range("R3:R15").Value
Range(Sheets("NFD").Cells(18, col), Sheets("NFD").Cells(30, col)) = Sheets("NF").Range("V3:V15").Value

Thanks a lot Mark. You got full MARKS. I owe beer.

 

[sudhakars]

tested and salute to Mark for his knowledge sharing.
Thanks Mark.

 

[MARK858]

You're welcome