Range copy issue

S

sudhakars

New Member
Joined
Feb 5, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Do you have a function named 'FindingLastRow' ? If not, then there is no value for the 'row' variable except 'empty'. If you do have a function for it, then please post it so it can be evaluated.
Apparently, you do have a function for 'getLastUsedCol' sincce it is producing results.
 
Upvote 0
JLGWhiz said:
Do you have a function named 'FindingLastRow' ? If not, then there is no value for the 'row' variable except 'empty'. If you do have a function for it, then please post it so it can be evaluated.
Click to expand...
it's there pfa code
Function FindingLastRow(sht As String)
Dim Ws As Worksheet, LastRow As Long
Set Ws = ThisWorkbook.Worksheets(sht)

' Here we look in Column A
LastRow = Ws.Cells(Ws.Rows.Count, "A").End(xlUp).row + 1
FindingLastRow = LastRow
End Function

same for lastcol code is there. It's working code but for 2sheets it's not working
 
Upvote 0
sudhakars said:
it's there pfa code
Function FindingLastRow(sht As String)
Dim Ws As Worksheet, LastRow As Long
Set Ws = ThisWorkbook.Worksheets(sht)

' Here we look in Column A
LastRow = Ws.Cells(Ws.Rows.Count, "A").End(xlUp).row + 1
FindingLastRow = LastRow
End Function

same for lastcol code is there. It's working code but for 2sheets it's not working
Click to expand...
Function getLastUsedCol(row As Byte, sht As String)

Dim Ws As Worksheet, last_col As Integer

Set Ws = ThisWorkbook.Worksheets(sht)

getLastUsedCol = Ws.Cells(row, Columns.Count).End(xlToLeft).Column + 1 'This line gets the last column

Debug.Print last_col

End Function
other fn
 
Upvote 0
You need to qualify all your Range/Cells references for example
VBA Code: 
.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
should be
Rich (BB code): 
.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
 
Upvote 0
Missed periods cause problems because those statements then refer to the activesheet, wherever that may be, and produce undesired results. Functions are OK, and when the periods are added to your statements inside the With, it should run OK.
 
Upvote 0
MARK858 said:
You need to qualify all your Range/Cells references for example
VBA Code: 
.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
should be
Rich (BB code): 
.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
Click to expand...
Tons of thanks. it worked but real time testing will be on Monday. You deserve one beer. and I owe you. Above code was from copy paste technology. I tried below code. I haven't used with there and it was also not working. It is was having same issue. If yes, why it's working for NF BNF sheets.

'NFD CODE
col = getLastUsedCol(3, "NFD")
Sheets("NFD").Cells(1, col) = Left(TimeValue(Now), 5)
Sheets("NFD").Cells(2, col) = Sheets("NF").Cells(1, 1).Value
Sheets("NFD").Cells(17, col) = Sheets("NF").Cells(1, 1).Value
Sheets("NFD").Cells(16, col) = Left(TimeValue(Now), 5)

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

'BNF CODE
col = getLastUsedCol(3, "BNFD")
Sheets("BNFD").Cells(1, col) = Left(TimeValue(Now), 5)
Sheets("BNFD").Cells(2, col) = Sheets("BNF").Cells(1, 1).Value
Sheets("BNFD").Cells(17, col) = Sheets("BNF").Cells(1, 1).Value

''Sheets("BNFD").Range(Cells(3, col), Cells(15, col)) = Sheets("BNF").Range("R3:R15").Value
''Sheets("BNFD").Range(Cells(18, col), Cells(30, col)) = Sheets("BNF").Range("V3:V15").Value
Sheets("BNFD").Cells(3, col) = Sheets("BNF").Range("R3:R16").Value
Sheets("BNFD").Range(Cells(18, col)) = Sheets("BNF").Range("V3:V16").Value

Sheets("BNFD").Cells(1, col) = Left(TimeValue(Now), 5)
'end Bnfd
 
Upvote 0
sudhakars said:
If yes, why it's working for NF BNF sheets.
Click to expand...
See comments in green

'BNF CODE
col = getLastUsedCol(3, "BNFD")
Sheets("BNFD").Cells(1, col) = Left(TimeValue(Now), 5)
'Single cell and so sheet is qualified
Sheets("BNFD").Cells(2, col) = Sheets("BNF").Cells(1, 1).Value
'Single cell and so sheet is qualified
Sheets("BNFD").Cells(17, col) = Sheets("BNF").Cells(1, 1).Value
'Single cell and so sheet is qualified
''Sheets("BNFD").Range(Cells(3, col), Cells(15, col)) = Sheets("BNF").Range("R3:R15").Value
'Line is commented out and so not being used
''Sheets("BNFD").Range(Cells(18, col), Cells(30, col)) = Sheets("BNF").Range("V3:V15").Value
'Line is commented out and so not being used
Sheets("BNFD").Cells(3, col) = Sheets("BNF").Range("R3:R16").Value
'Single cell and so sheet is qualified, although you are trying to apply multiple values to a single cell.
Sheets("BNFD").Range(Cells(18, col)) = Sheets("BNF").Range("V3:V16").Value
'Single cell and so Range not necessary, should be
VBA Code: 
Sheets("BNFD").Cells(18, col) = Sheets("BNF").Range("V3:V16").Value
'although you are trying to apply multiple values to a single cell.
 
Upvote 0
sudhakars said:
Sheets("NFD").Range(Cells(3, col), Cells(15, col)) = Sheets("NF").Range("R3:R15").Value
Sheets("NFD").Range(Cells(18, col), Cells(30, col)) = Sheets("NF").Range("V3:V15").Value
Click to expand...
In this part I am copying range to range but copy is not working on non active NFD sheet
 
Upvote 0
You are not defining the sheets with the Cells again
Rich (BB code): 
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
Rich (BB code): 
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Solved
Conditional Formatting Issue
Replies
7
Views
126
etaf
etaf
JenniferMurphy
  • Question
How to synchronize two tables?
Replies
0
Views
109
JenniferMurphy
JenniferMurphy
E
  • Solved
Spilled formula to return the min of a reducing range
Replies
3
Views
84
Fluff
Fluff
J
  • Question
sumifs with pivot table
Replies
3
Views
141
RoryA
RoryA
A
  • Solved
distrubuting amounts from textbox on user form to column inside sheet
Replies
2
Views
163
abdo meghari
A

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top