how to copy and paste a DATA from one sheet to another

[charith]

hello...

i want to copy column A,D,E,F from sheet 1 to sheet 2 column B,C,D,F...

I have a sheet titled "Main" that contains the raw data like the example below. I have another sheets titled "Orders" and . I'm needing a code to copy the data only from column A,D,E,F in "Main" sheet, and paste it in the "Sheet 2" column B,C,D,F based on "sell & buy" values

Sheet(main)

A	B	C	D	E	F
ORDER TYPE			#ORDERS	PRICE	%
BUY			1	45.78	0.1
SELL			23	2354.67	0.15
-			-	-	-
BUY			345	6789	0.23

<tbody>
</tbody>

Sheet(Orders)

A	B	C	D	F	G
A1	BUY	1	45.78	0.1
B2	SELL	23	2354.67	0.15
C1	BUY	345	6789	0.23
D1

<tbody>
</tbody>

​Any help is MUCH appreciated THANK YOU!!

 

[charith]

Post a link to your most recent workbook example please.

Along with the columns you want copies from and to with the 10 second delay code.

Howard

https://www.dropbox.com/s/x6vxj3zm5jph04v/LIVE%20STOCK%20QUOTES%20%28API%29%20test.xlsm

 

[L. Howard]

Okay, got the latest version and I notice that the bracket you show in your post is [...] and the bracket in column AW is an accounting bracket to show a negative value.

In my tests this works, but it is looking for a [.
(Module 2 code for left bracket [ in col AW)

For Each c In Arng
  For Each c In Brng
  
  If c.Offset(, -3) = "" Then
    If Left(c, 1) <> "[" Then
      c.Offset(, -3) = c
    End If
  End If
  
Next

I think you need this instead.
Test it and see if it works for you.
(Module 2 code or negative value in col Aw)

For Each c In Arng
  For Each c In Brng
  
  If c.Offset(, -3) = "" Then
    If c > 0 Then
      c.Offset(, -3) = c
    End If
  End If
  
Next

Howard

 

[L. Howard]

Here is my revised version for column AW negative values. See the very small button which I used because I was not sure what button you had the code assigned.

https://www.dropbox.com/s/bgj1vvdyf... Negative Values in Col AW code Drop Box.xlsm

Howard

 

[charith]

that is great sir..

but i want to update the open price column once current price value changed from (1.00) to another value , then copy that value to "open price" column like this

Open price	Current Price
123.56	123.56
-1	[1.00]
1245.78	1245.78
789.45	789.45

<tbody>
</tbody>

Open price	Current Price
123.56	123.56
345.69	345.69
1245.78	1245.78
789.45	789.45

<tbody>
</tbody>

can you please hep me to do that sir...

Thank you very much

 

[charith]

bump

 

[L. Howard]

Try this in sheet the Main code module.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AW:AW,BI48:BJ65")) Is Nothing _
   Or Target.count > 1 Then Exit Sub
    
Dim rngAW As Range
Dim LRow As Long
Dim c As Range

LRow = Cells(Rows.count, 49).End(xlUp).Row
Set rngAW = Range("AW6:AW" & LRow)

Select Case Target.Column
   Case 49

      For Each c In rngAW
         If c.Offset(, -3) = "" Then
            If c > 0 Then
               c.Offset(, -3) = c
            End If
         End If
      Next
    
   Case 61, 62
      Cells(Target.Row, 46) = Cells(Target.Row, 49)
End Select

End Sub

 

[charith]

sir it works fine.. Thanks a lot for your great help..!!

 

[charith]

sir if i want to check the column 61 > 0 or column 62 > 0 when coping data from current price to open price ( Cells(Target.Row, 46) = Cells(Target.Row, 49)) what are the changes that i have to do in that code

Thank you very much

 

[L. Howard]

Plug this in the code and see if it works for you.

You say you want to check if cells 61 and 62 are > 0. I assume if cells 61 or 62 are less than 0 then you do not want 46 to = 49.

This seems to do that in my tests.

Case 61, 62
     If Cells(Target.Row, 61) < 0 Then Exit Sub
     If Cells(Target.Row, 62) < 0 Then Exit Sub
        Cells(Target.Row, 46) = Cells(Target.Row, 49)

Howard

 

[charith]

Plug this in the code and see if it works for you.

You say you want to check if cells 61 and 62 are > 0. I assume if cells 61 or 62 are less than 0 then you do not want 46 to = 49.

This seems to do that in my tests.

Case 61, 62
     If Cells(Target.Row, 61) < 0 Then Exit Sub
     If Cells(Target.Row, 62) < 0 Then Exit Sub
        Cells(Target.Row, 46) = Cells(Target.Row, 49)

Howard

Thank you very much sir..
but it is not working... i tried like this also

Case 61, 62
     If Cells(Target.Row, 61)  = "" Then Exit Sub
     If Cells(Target.Row, 62)  = "" Then Exit Sub
        Cells(Target.Row, 46) = Cells(Target.Row, 49)

but it also didn't work ...!!

actually i want to cancel coping from column 49 ti column 46 when there is no data in column 61 and column 62

column 46(open price)	column 49(current price)	column 61(day high)	column 62(day low)
123.56	123.56	756	897
457.78	457.78	890	345
	1233	-	-
568	568	90	234

<tbody>
</tbody>

Thank you very much