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!!

 

[L. Howard]

You can try here, I had no luck with it.


26. VBA Tips - Call Code From Another Workbook | ExcelExperts.com

Howard

 

[L. Howard]

You will have to make sure the workbook names in the code are correct.

You probably have to remove the part that says Drop Box from the names and make sure all the workbook names are proper.

The code will work if the names of the workbooks are correct. I runs fine on my computer here. When I send it to you is when the names get screwed up.

Howard

 

[charith]

sir finally it works..!! this is the code..!!

Sub Macro4()


    Workbooks.Open ("C:\Users\charith\Google Drive\Analysis\Long & Short\Long\A.xlsx")
    Windows("A.xlsx").Activate
    Range("J65:AY65").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M2").Select
    ActiveSheet.Paste Link:=True
    Range("M2").Select
    ActiveWorkbook.Save
    Windows("A.xlsx").Close
    
    Workbooks.Open ("C:\Users\charith\Google Drive\Analysis\Long & Short\Long\AA.xlsx")
    Windows("AA.xlsx").Activate
    Range("J65:AY65").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M3").Select
    ActiveSheet.Paste Link:=True
    Range("M3").Select
    ActiveWorkbook.Save
    Windows("AA.xlsx").Close
    
    Workbooks.Open ("C:\Users\charith\Google Drive\Analysis\Long & Short\Long\ADBE.xlsx")
    Windows("ADBE.xlsx").Activate
    Range("J65:AY65").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M4").Select
    ActiveSheet.Paste Link:=True
    Range("M4").Select
    ActiveWorkbook.Save
    Windows("ADBE.xlsx").Close
    
    Workbooks.Open ("C:\Users\charith\Google Drive\Analysis\Long & Short\Long\ALXN.xlsx")
    Windows("ALXN.xlsx").Activate
    Range("J65:AY65").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M5").Select
    ActiveSheet.Paste Link:=True
    Range("M5").Select
    ActiveWorkbook.Save
    Windows("ALXN.xlsx").Close

sir can't we use a loop to open these files. like this ..!!

Dim i As Long
i = 3
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = "C:\Users\Analysis\Long & Short\Long\"
Filename = Dir(Path & "*.xlsx")




'-----------------------------<wbr>---------------
'OPEN EXCEL FILES IN FOLDER
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
''''''''''''''''''''''''''''''<wbr>'''''''''''''''''''''''''''''
'''''''''''''''Open Files '''''''''''''''''''''''''
With ActiveWorkbook
If Not IsEmpty(.LinkSources(<wbr>xlExcelLinks)) Then
For Each link In .LinkSources(xlExcelLinks)
.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
End With

thank you very much

 

[L. Howard]

You have lost me, I don't know what code is working for you or what is not.

Howard

 

[charith]

sir this code is working...!!

Sub Macro4()


    Workbooks.Open ("C:\Users\charith\Google Drive\Analysis\Long & Short\Long\A.xlsx")
    Windows("A.xlsx").Activate
    Range("J65:AY65").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M2").Select
    ActiveSheet.Paste Link:=True
    Range("M2").Select
    ActiveWorkbook.Save
    Windows("A.xlsx").Close
    
    Workbooks.Open ("C:\Users\charith\Google Drive\Analysis\Long & Short\Long\AA.xlsx")
    Windows("AA.xlsx").Activate
    Range("J65:AY65").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M3").Select
    ActiveSheet.Paste Link:=True
    Range("M3").Select
    ActiveWorkbook.Save
    Windows("AA.xlsx").Close
End  sub

 

[L. Howard]

This is working for me with the Book A, Book AA & long.

See the button near column M on the long sheet 1.

I colored the cells to copy to red for me to better see them. You will want to change that. The scattered cells do not copy the format so they do show up as red on sheet long.

There is also a button on A & AA that will copy only that sheet info to long.



https://www.dropbox.com/s/c0vd0kp38p6mmk6/Book_A.xlsm

https://www.dropbox.com/s/phrx4aj6nsh14dh/Book_AA.xlsm

Howard

 

[L. Howard]

Here is the long sheet


https://www.dropbox.com/s/2nnb0khdzdlpf0z/long.xlsm

Howard

 

[L. Howard]

With this copy of long, it may open as "Copy of long (1)"

If it does, then do a Save As and save as "long" only, no quotes.

Then it should work with the Book A & AA.

Howard

 

[charith]

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

 

[charith]

hii sir i need a big help from you..




i want to copy last prices to exit prices , only when SIGNAL = S,
but at the beginning SIGNAL column = empty after some time , if any cell in that column become "S" THEN copy the last prices to exit prices


beginning

HIGH PRICE	LAST PRICE	SIGNAL	EXIT PRICE
12	78
56	34
34	56

<tbody>
</tbody>

After some time

HIGH PRICE	LAST PRICE	SIGNAL	EXIT PRICE
12	78
56	34	S	34
34	56

<tbody>
</tbody>

HIGH PRICE	LAST PRICE	SIGNAL	EXIT PRICE
12	78	S	78
56	34	S	34
34	56

<tbody>
</tbody>