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]

Copy this to a Standard Module, not the sheet module. I used a Forms button with the Sub TenSec() assigned to it.

The button needs to be on the sheet you want the copy code to run on.

So, if you had a button on three different sheets and TenSec was assigned to each button, the code would work on the sheet of the button clicked and not the other two.

This may not be in best practice of coding. Maybe one of the pros will chime in and make a suggestion.

Howard

Option Explicit

Sub TenSec()
 'Runs a macro 10 seconds after button click.

Application.OnTime Now + TimeValue("00:00:10"), "AcolToBcol"

End Sub

Sub AcolToBcol()
Dim c As Range
Dim Lc As Long, lr As Long
Dim Brng As Range
Dim lColumn As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row
Set Brng = Range("B1:B" & lr)

For Each c In Brng
  
  If c <> "" Then
     c.Copy c.Offset(, -1)
  End If
  
Next

End Sub

Thank you very much for your code sir
i tried like this .. but it didn't work..!!

https://www.dropbox.com/s/hrfsh4fy7hbdzif/test.xlsx

 

[charith]

bump

 

[L. Howard]

See if this works.

Howard

https://www.dropbox.com/s/nb1rpkmhxmdnw08/Copy of test Drop Box.xlsm

 

[charith]

See if this works.

Howard

https://www.dropbox.com/s/nb1rpkmhxmdnw08/Copy%20of%20test%20Drop%20Box.xlsm

Thank you very much sir.. it works..!!

 

[charith]

Sir if i want to copy Column B (2)("Main sheet") -> Column A (1)("Order sheet") AND Column AV(48)("Main sheet") -> Column E(5)("Order sheet")

what are the changes that i have to done in this code

Sub BuyCells()
Dim c As Range
Dim ceBySel As Range
Dim lr As Long


lr = Cells(Rows.Count, 83).End(xlUp).Row
Set ceBySel = Range("CE6:CE" & lr)


Application.ScreenUpdating = False


For Each c In ceBySel


If c = "BUY" Or c = "SELL" Then
c.Resize(1, 3).Copy
Sheets("Orders").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

End If


Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[charith]

bump

 

[L. Howard]

Add this line just below the other one in the code you posted.

  c.Offset(, -35).Copy
         Sheets("Orders").Range("E" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Also, I think it's time for you to learn to use code tags when you post code to the forum.
It make it much easier to read and preserves the spaces etc.

Just type in Code in the brackets as shown and type or copy your code between the bracketed code words.
I've added space so it will not compile. When you use the code tags type with NO spaces and it will look like the code I posted above.

[ code ] Your code goes here [ /code ]

Howard

 

[charith]

Add this line just below the other one in the code you posted.

  c.Offset(, -35).Copy
         Sheets("Orders").Range("E" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Also, I think it's time for you to learn to use code tags when you post code to the forum.
It make it much easier to read and preserves the spaces etc.

Just type in Code in the brackets as shown and type or copy your code between the bracketed code words.
I've added space so it will not compile. When you use the code tags type with NO spaces and it will look like the code I posted above.

[ code ] Your code goes here [ /code ]

Howard

Thank you very much sir, it works

 

[charith]

sir,
i have a small problem in these two columns

when the market starts, some current prices are loaded to this sheet with few minutes delay (sometimes it late more than 2 minutes),those prices are representing -1 ([1.00]) <- this is not the actual price

Open price	Current Price
	123.56
	[1.00]
	1245.78
	789.45

<tbody>
</tbody>

once i pressed the "GET OPEN PRICE" button it will copy the "current price " data to " open price " after 10 seconds ,like this ..

Open price	Current price
123.56	123.56
1.00	[1.00]
1245.78	1245.78
789.45	789.45

<tbody>
</tbody>

so what i want is when there is a data in "Current price" column which = [1.00] ,then do not copy that data to "Open price"

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

<tbody>
</tbody>

so once it changed from [1.00] to another value , then copy to "open price"

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

<tbody>
</tbody>

Sir, can you please help me to do that..!!

Thank you very much..!!

 

[L. Howard]

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