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

C

charith

Board Regular
Joined
Jan 3, 2014
Messages
152
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)
ABCDEF
ORDER TYPE#ORDERSPRICE%
BUY145.780.1
SELL232354.670.15
----
BUY34567890.23

<tbody>
</tbody>

Sheet(Orders)
ABCDFG
A1BUY145.780.1
B2SELL232354.670.15
C1BUY34567890.23
D1

<tbody>
</tbody>

​Any help is MUCH appreciated THANK YOU!!
 
Sort by date Sort by votes

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Give this a try.

The "Y" in column 104 is produced by a formula in column 104. The worksheet Change event does not see that as a "Change" and therefore does not run the code.

This code looks at columns BI and BJ, (see the red code lines) and if there is a change there (the same change that makes the formula in 104 to return a "Y") it will copy the current price(AW) to open price(AT).

Column 104 is completely out of the picture as far as the change event is concerned. It is really only for your visual reference.

Replace the code in your worksheet with the code below.

Notice I have included Option Explicit which should be the first line in the vb editor. it is good practice.

Howard

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
With Target

[COLOR=#ff0000]If Not Intersect(Target, Range("BI48:BJ65")) Is Nothing Then
    Cells(.Row, 46) = Cells(.Row, 49)
End If[/COLOR]

If (.Column = 59 And .Value = "S") Then
    Cells(.Row, 85) = Cells(.Row, 51)
    Cells(.Row, 84) = Cells(.Row, 73)
    Cells(.Row, 83) = "SELL"
    
End If
If (.Column = 59 And .Value = "L") Then
    Cells(.Row, 85) = Cells(.Row, 52)
    Cells(.Row, 84) = Cells(.Row, 72)
    Cells(.Row, 83) = "BUY"
    
End If

End With
End Sub
 
Upvote 0
L. Howard said:
Give this a try.

The "Y" in column 104 is produced by a formula in column 104. The worksheet Change event does not see that as a "Change" and therefore does not run the code.

This code looks at columns BI and BJ, (see the red code lines) and if there is a change there (the same change that makes the formula in 104 to return a "Y") it will copy the current price(AW) to open price(AT).

Column 104 is completely out of the picture as far as the change event is concerned. It is really only for your visual reference.

Replace the code in your worksheet with the code below.

Notice I have included Option Explicit which should be the first line in the vb editor. it is good practice.

Howard

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
With Target

[COLOR=#ff0000]If Not Intersect(Target, Range("BI48:BJ65")) Is Nothing Then
    Cells(.Row, 46) = Cells(.Row, 49)
End If[/COLOR]

If (.Column = 59 And .Value = "S") Then
    Cells(.Row, 85) = Cells(.Row, 51)
    Cells(.Row, 84) = Cells(.Row, 73)
    Cells(.Row, 83) = "SELL"
    
End If
If (.Column = 59 And .Value = "L") Then
    Cells(.Row, 85) = Cells(.Row, 52)
    Cells(.Row, 84) = Cells(.Row, 72)
    Cells(.Row, 83) = "BUY"
    
End If

End With
End Sub
Click to expand...
Finally i think it will work fine(as the stock market close now)
Thanks a lot for your great help...
Thank you very much Sir...!!!
 
Upvote 0
one last question..!!

i want to add these event to two command buttons..!
first i add this to command button 01

Private Sub CommandButton1_Click()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents = False Then Me.CommandButton1.Caption = "Get Open price-ON": Me.CommandButton1.BackColor = 65535
If Application.EnableEvents = True Then Me.CommandButton1.Caption = "Get Open price-OFF": Me.CommandButton1.BackColor = 50000
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
With Target


If Not Intersect(Target, Range("BI48:BJ65")) Is Nothing Then
Cells(.Row, 46) = Cells(.Row, 49)
End If


End With
End Sub

but i want to add this event to command button as well



Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If (.Column = 59 And .Value = "S") Then
Cells(.Row, 85) = Cells(.Row, 51)
Cells(.Row, 84) = Cells(.Row, 73)
Cells(.Row, 83) = "SELL"

End If
If (.Column = 59 And .Value = "L") Then
Cells(.Row, 85) = Cells(.Row, 52)
Cells(.Row, 84) = Cells(.Row, 72)
Cells(.Row, 83) = "BUY"

End If


End With
End Sub



so i tried to add it to a new module like this

Private Sub CommandButton2_Click()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents = False Then Me1.CommandButton2.Caption = "ON": Me1.CommandButton2.BackColor = 65535
If Application.EnableEvents = True Then Me1.CommandButton2.Caption = "OFF": Me.CommandButton2.BackColor = 50000
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If (.Column = 59 And .Value = "S") Then
Cells(.Row, 85) = Cells(.Row, 51)
Cells(.Row, 84) = Cells(.Row, 73)
Cells(.Row, 83) = "SELL"

End If
If (.Column = 59 And .Value = "L") Then
Cells(.Row, 85) = Cells(.Row, 52)
Cells(.Row, 84) = Cells(.Row, 72)
Cells(.Row, 83) = "BUY"

End If


End With
End Sub

But it didn't work.. can any one please tell me how to add two command buttons to same sheet for run two different events..
 
Upvote 0
It is not clear to me what you are trying to do.

But here are some thing to remember.

You can have only one Private Sub Worksheet_Change(ByVal Target As Range) in a sheet module.

You cannot put Private Sub Worksheet_Change(ByVal Target As Range) in a button control.

As far as I know you could do something like this, where you put the code that was a change event in the button control so when you click the button you do the same as the change event macro did.

The first example below will not work, it just shows how it would look.
The code will need to be re written because it uses Target and you do not have (ByVal Target As Range) in the sub name.


I am not sure if the second one will work. You will need to test it.

Code: 
Private Sub CommandButton1_Click()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents = False Then Me.CommandButton1.Caption = "Get Open price-ON": Me.CommandButton1.BackColor = 65535
If Application.EnableEvents = True Then Me.CommandButton1.Caption = "Get Open price-OFF": Me.CommandButton1.BackColor = 50000

If Not Intersect(Target, Range("BI48:BJ65")) Is Nothing Then
 Cells(.Row, 46) = Cells(.Row, 49)
End If

End Sub



Private Sub CommandButton2_Click()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents = False Then Me1.CommandButton2.Caption = "ON": Me1.CommandButton2.BackColor = 65535
If Application.EnableEvents = True Then Me1.CommandButton2.Caption = "OFF": Me.CommandButton2.BackColor = 50000

If (.Column = 59 And .Value = "S") Then
 Cells(.Row, 85) = Cells(.Row, 51)
 Cells(.Row, 84) = Cells(.Row, 73)
 Cells(.Row, 83) = "SELL"
End If

If (.Column = 59 And .Value = "L") Then
 Cells(.Row, 85) = Cells(.Row, 52)
 Cells(.Row, 84) = Cells(.Row, 72)
 Cells(.Row, 83) = "BUY"
End If

End Sub

Howard
 
Upvote 0
i tested both but it gives me a compile error called " invalid or unqualified reference" it highlighted "Row" as below

Private Sub CommandButton1_Click()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents = False Then Me.CommandButton1.Caption = "Get Open price-ON": Me.CommandButton1.BackColor = 65535
If Application.EnableEvents = True Then Me.CommandButton1.Caption = "Get Open price-OFF": Me.CommandButton1.BackColor = 50000


If Not Intersect(Target, Range("BI48:BJ65")) Is Nothing Then
Cells(.Row, 46) = Cells(.Row, 49)
End If


End Sub






Private Sub CommandButton2_Click()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents = False Then Me1.CommandButton2.Caption = "ON": Me1.CommandButton2.BackColor = 65535
If Application.EnableEvents = True Then Me1.CommandButton2.Caption = "OFF": Me.CommandButton2.BackColor = 50000


If (.Column = 59 And .Value = "S") Then
Cells(.Row, 85) = Cells(.Row, 51)
Cells(.Row, 84) = Cells(.Row, 73)
Cells(.Row, 83) = "SELL"
End If


If (.Column = 59 And .Value = "L") Then
Cells(.Row, 85) = Cells(.Row, 52)
Cells(.Row, 84) = Cells(.Row, 72)
Cells(.Row, 83) = "BUY"
End If


Actually i want to turn ON/OFF these two events separately.. That's why i use two buttons..
 
Last edited:
Upvote 0
Maybe this.


Code: 
Option Explicit

Private Sub CommandButton1_Click()
 Application.EnableEvents = Not Application.EnableEvents
 If Application.EnableEvents = False Then Me.CommandButton1.Caption = "Get Open price-ON": Me.CommandButton1.BackColor = 65535
 If Application.EnableEvents = True Then Me.CommandButton1.Caption = "Get Open price-OFF": Me.CommandButton1.BackColor = 50000


Dim c As Range
Dim cR As Long

  For Each c In Range("BI48:BJ65")
   cR = c.Row
     If c <> "" Then
        Cells(cR, 46) = Cells(cR, 49)
     End If
 Next

End Sub
 
Upvote 0
L. Howard said:
Maybe this.


Code: 
Option Explicit

Private Sub CommandButton1_Click()
 Application.EnableEvents = Not Application.EnableEvents
 If Application.EnableEvents = False Then Me.CommandButton1.Caption = "Get Open price-ON": Me.CommandButton1.BackColor = 65535
 If Application.EnableEvents = True Then Me.CommandButton1.Caption = "Get Open price-OFF": Me.CommandButton1.BackColor = 50000


Dim c As Range
Dim cR As Long

  For Each c In Range("BI48:BJ65")
   cR = c.Row
     If c <> "" Then
        Cells(cR, 46) = Cells(cR, 49)
     End If
 Next

End Sub
Click to expand...
Thanks a lot sir, I think this will work..!! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Copy selected item from combobox on userform to specific column for multiple words
Replies
0
Views
126
Omar M
O
Smerdis13
  • Question
VBA to Get Output by check relations from Input
Replies
2
Views
163
Smerdis13
Smerdis13
A
  • Question
Macro help
Replies
9
Views
324
Peter_SSs
Peter_SSs
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
134
JMC57
J
D
  • Question
finding same data on another sheet but with exceptions
Replies
0
Views
246
daveyc18
D

Forum statistics

Threads
1,215,368
Messages
6,124,523
Members
449,169
Latest member
mm424

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