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]

Sir , i use this code to copy 83,84,and 85 columns (Order Type, #Order ,Price) in "Main sheet " to 2, 3, 4 columns in "Order sheet"
but it copied only the 83 column only

Option Explicit


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


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


Application.ScreenUpdating = False


For Each c In rngA


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

c.Offset(, 83).Resize(1, 2).Copy
Sheets("Orders1").Range("G" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues


End If


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

 

[L. Howard]

Try this.

Option Explicit

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

 lr = Cells(Rows.Count, 1).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]

Try this.

Option Explicit

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

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

 Application.ScreenUpdating = False

 For Each c In ceBySel

    If c = "BUY" Or c = "SELL" Then <-Run time error 
           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

Thank you very much sir

 

[L. Howard]

Looks like I missed a small detail on this line.

Change the 1 to 83 and se if that makes a difference.

 lr = Cells(Rows.Count, [COLOR="#FF0000"]83[/COLOR]).End(xlUp).Row

Howard

 

[L. Howard]

Try the button near column 83 on this version of your workbook.

https://www.dropbox.com/s/5u6a1vt8z0izsq1/LIVE STOCK QUOTES Column 83 code Drop Box.xlsm

Howard

 

[L. Howard]

The headers on the Orders sheet seem to be in error, should be this, but the code will still work with the faulty headers.

 Symbol	Action	Quantity	Lmt Price	Gap

Howard

 

[charith]

Thank you very much sir.. it works..!! Thanks a lot

 

[L. Howard]

You're welcome. Hope it all comes together for you.

Regards,
Howard

 

[charith]

sir ,

Hi, I have a pretty tough request,

What I would like to do is have a button that I could press that once pressed starts an event which copied the all the data in column B into column A exactly 10 seconds later ..!!

i tried this code

but it didn't work

Private Sub CommandButton1_Click()
Dim x As Variant, _
LR As Long

If Range("B1") = "" Then
Range("B:B").Value = Range("A:A").Value
Application.OnTime Now() + TimeValue("00:00:10"), "OnTimeFill", , True
End If
End Sub

Thank you very much ..!!

 

[L. Howard]

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