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]

bump

 

[L. Howard]

Here is a clean example workbook to show how the code works.

https://www.dropbox.com/s/xiqgbeuxyez4qoa/LIVE STOCK QUOTES Orders Example Drop Box.xlsm


Click the button and check Orders sheet, click again, check Orders sheet and see that nothing has be copied twice.

Now add in a couple more BUY or SELL in column CE. Click button and those new BUY SELL are copied.

See the small list of #N/A and #DIV/0! formula error signs.

Copy one or two of these into column CE. Click the button and you get a code error.

So those formula error signs returns are causing the problem.

I am not sure how to write code to ignore them. Maybe one of the many viewers of this thread can add something to the code to ignore the formula error signs.

And you could write the formulas to return "" (nothing) or a 0 (zero) instead of the error signs.

Howard

 

[L. Howard]

Here is a code to try. I think this will do the trick.

Notice Teeroy name in the sub title. He offered it up and I have tested it and it works fine on the workbook you sent me.

Delete all the other BuyCells codes we have been trying and use this one.

Let me know how it goes.

Howard

Sub BuyCellsTeeroyNAerror()
Dim c
Dim cO As Range
Dim ceBySel As Range
Dim ceBySelO As Range
Dim lr As Long
Dim lrO As Long

lrO = Sheets("Orders").Cells(Rows.count, 1).End(xlUp).Row
Set ceBySelO = Sheets("Orders").Range("A2:A" & lrO)

Application.ScreenUpdating = False

With Sheets("Main")
    lr = .Range("CE" & Rows.count).End(xlUp).Row
    Set ceBySel = .Range("CE6:CE" & lr)
End With
With Sheets("Orders")
    For Each c In ceBySel
        If Not IsError(c) Then 'new error wrapper
            If WorksheetFunction.CountIf(ceBySelO, c.Offset(, -81)) < 1 _
              And (c.Value = "BUY" Or c.Value = "SELL") Then
                  .Range("B" & Rows.count).End(xlUp)(2).Resize(1, 3).Value = c.Resize(1, 3).Value
                  .Range("E" & Rows.count).End(xlUp)(2).Value = c.Offset(, -35).Value
                  .Range("A" & Rows.count).End(xlUp)(2).Value = c.Offset(, -81).Value
                  Set ceBySelO = ceBySelO.Resize(ceBySelO.Rows.count + 1, 1)
            End If
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

 

[charith]

Thank you very much sir, i,ll try

 

[charith]

Thank you very much sir, finally it works sir... thanks a lot sir...!!!

 

[L. Howard]

With that code you can delete the formulas in column CD. The code uses column B and range ceBySelO to make sure there is no double copy.

Glad its working for you.

Howard

 

[charith]

oki sir.. anyway Thanks a lot for your great help sir

 

[charith]

hello sir..!!
i have a small problem in this workbook.. https://www.dropbox.com/s/m8qbeayyuolmyyg/Book1.xlsx
when i press "OPEN " button , i want to open the files in the column "A" , and run the solver function in those files save and close them..!!!

can you please help me to do that sir..!!!

Thank you very much sir..!!!

 

[charith]

bump

 

[L. Howard]

You would do better to start a new thread with a title something like:

Loop through list of files in Col A and run a macro that is in each file.

Include the link as you have in your post here.

Howard