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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
sir.. i have a problem in coping data from multiple files..!! i use this code to copy data from two files ("A" & "AA")
but it copied data only when both files are open..!! but once i close those filed it gives me errors...!!

Code: 
Sub Macro1()

    Range("AV2").Select
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("M2").Select
    ActiveSheet.Paste Link:=True
 
    Windows("A.xlsx").Activate
    Range("AW4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("N2").Select
    ActiveSheet.Paste Link:=True
    
    Windows("A.xlsx").Activate
    Range("X20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("O2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("V20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("P2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("AN20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("Q2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("AO20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("R2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("X8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("S2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("V8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("T2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("AF2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("U2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("AG4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("V2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("C7:C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("Y2:AJ2").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("A.xlsx").Activate
    Range("C33:C50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("AK2:AX2").Select
    ActiveSheet.Paste Link:=True
    
  
 
    
    Windows("AA.xlsx").Activate
    Range("AW4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("N3").Select
    ActiveSheet.Paste Link:=True
    
    Windows("AA.xlsx").Activate
    Range("X20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("O3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("V20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("P3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("AN20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("Q3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("AO20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("R3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("X8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("S3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("V8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("T3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("AF2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("U3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("AG4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("V3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("C7:C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("Y3:AJ3").Select
    ActiveSheet.Paste Link:=True
    
    
    Windows("AA.xlsx").Activate
    Range("C33:C50").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Portfolio for analysis.xlsx").Activate
    Range("AK3:AX3").Select
    ActiveSheet.Paste Link:=True
    
    
End Sub
 
Upvote 0
Can you post a link to both files and tell me what you are trying to do.

Please be exact when explaining what you want to happen.

Howard
 
Upvote 0
sir, i want to copy following cells in "A" and "AA" files and save it on the row 2 and row 3(from )
 
Upvote 0
sir, i want to copy following cells in "A" (https://www.dropbox.com/s/j8tp0x76ovyjzqy/A.xlsx) and "AA" (https://www.dropbox.com/s/sge5w73rvo2qt8n/AA.xlsx) files and paste it on the row 2 and row 3(paste start from M2)in "long" file (https://www.dropbox.com/s/ewsqbogg8onnpwh/long.xlsx)

TO "A" file


AV2 ->M2
AW4
X20
V20
AN2
AO4
X8
AF2
AG4 ->u2

** skip column "V" and "W"
C7 to C18 -> X2 to AI2
C33 to C50 ->AJ2 to AZ2


like this i want to copy all the above mention cells in files which are showed in column "B" in long file (https://www.dropbox.com/s/ewsqbogg8onnpwh/long.xlsx)

THANK YOU VERY MUCH
 
Upvote 0
Thanks a lot sir..!!
but it gives me "Subscript out of range" error :(

sir, can you add a one button in "long" file to copy all the data in "A" and other files at once...!!
 
Upvote 0
sir, i tried this code.. but it also didn't work..!!(no errors) :(

Code: 
[FONT=verdana]Sub CopyData()[/FONT]
[FONT=verdana]'DECLARE AND SET VARIABLES[/FONT]
[FONT=verdana]Dim i As Long[/FONT]
[FONT=verdana]i = 3[/FONT]
[FONT=verdana]Dim wbk As Workbook[/FONT]
[FONT=verdana]Dim Filename As String[/FONT]
[FONT=verdana]Dim Path As String[/FONT]
[FONT=verdana]Path = "C:\Users\charith\Google Drive\Analysis\Long & Short\Long\"  'all that files (eg: "A", "AA") in "long" folder[/FONT]
[FONT=verdana]Filename = Dir([/FONT][FONT=verdana]"C:\Users\charith\Google Drive\Analysis\Long & Short\Long\"[/FONT][FONT=verdana]*.xlsx")[/FONT]
[FONT=verdana]
[/FONT]
[FONT=verdana]
[/FONT]
[FONT=verdana]'-----------------------------<wbr>---------------[/FONT]
[FONT=verdana]'OPEN EXCEL FILES IN FOLDER[/FONT]
[FONT=verdana] Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN[/FONT]
[FONT=verdana]    Set wbk = Workbooks.Open([/FONT][FONT=verdana]"C:\Users\charith\Google Drive\Analysis\Long & Short\Long\"[/FONT][FONT=verdana]*.xlsx"[/FONT][FONT=verdana])[/FONT]
[FONT=verdana]    ''''''''''''''''''''''''''''''<wbr>'''''''''''''''''''''''''''''[/FONT]
[FONT=verdana]    '''''''''''''''Open Files '''''''''''''''''''''''''[/FONT]
[FONT=verdana]    With ActiveWorkbook[/FONT]
[FONT=verdana]    If Not IsEmpty(.LinkSources(<wbr>xlExcelLinks)) Then[/FONT]
[FONT=verdana]    For Each link In .LinkSources(xlExcelLinks)[/FONT]
[FONT=verdana]    .BreakLink link, xlLinkTypeExcelLinks[/FONT]
[FONT=verdana]    Next link[/FONT]
[FONT=verdana]    End If[/FONT]
[FONT=verdana]    End With[/FONT]
[FONT=verdana]    ''''''''''''''''''Copy''''''''<wbr>'''''''''''''''''''''''[/FONT]
[FONT=verdana]    Sheets("sheet1").Select[/FONT]
[FONT=verdana]    Range("J65:AY65").Select    ' here i copy & paste all the cell values that i want to copy  in to one place  [/FONT]
[FONT=verdana]    Selection.Copy[/FONT]


[FONT=verdana]   ''''''''''''''''''Paste''''''<wbr>'''''''''''''''''''''''''' [/FONT]
[FONT=verdana]    Workbooks("long.xlsm").Activate[/FONT]
[FONT=verdana]    Sheets("sheet1").Select[/FONT]
[FONT=verdana]    Cells(i, 1).Value = Filename[/FONT]
[FONT=verdana]    Cells(i, 2).Select[/FONT]
[FONT=verdana]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/FONT]
[FONT=verdana]        :=False, Transpose:=False[/FONT]

[FONT=verdana]    i = i + 1[/FONT]
[FONT=verdana]    wbk.Close True[/FONT]
[FONT=verdana]    Filename = Dir[/FONT]
[FONT=verdana]Loop[/FONT]
[FONT=verdana]End Sub[/FONT]

"A" file https://www.dropbox.com/s/7cktnzv5osc7vot/A.test.xlsx
 
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
146
Omar M
O
Smerdis13
  • Question
VBA to Get Output by check relations from Input
Replies
2
Views
182
Smerdis13
Smerdis13
M
  • Solved
valid data input
Replies
3
Views
84
mwvirk
M
A
  • Question
Macro help
Replies
9
Views
358
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
165
JMC57
J

Forum statistics

Threads
1,216,126
Messages
6,129,008
Members
449,480
Latest member
yesitisasport

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