how to copy and paste a DATA from one sheet to another

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

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try this in the worksheet module of sheet1.

Regards,
Howard

Code:
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("A2:A" & lr)
'rngA.Select
Application.ScreenUpdating = False
For Each c In rngA
   If c = "BUY" Or c = "SELL" Then
     c.Copy
       Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     
     c.Offset(, 3).Resize(1, 2).Copy
       Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
      c.Offset(, 5).Copy
        Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
   End If

Next
Application.ScreenUpdating = True
End Sub
 

charith

Board Regular
Joined
Jan 3, 2014
Messages
152
Try this in the worksheet module of sheet1.

Regards,
Howard

Code:
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("A2:A" & lr)
'rngA.Select
Application.ScreenUpdating = False
For Each c In rngA
   If c = "BUY" Or c = "SELL" Then
     c.Copy
       Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     
     c.Offset(, 3).Resize(1, 2).Copy
       Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
      
      c.Offset(, 5).Copy
        Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
   End If

Next
Application.ScreenUpdating = True
End Sub
Thnkyou very ,much sir, but it didn't work ..!! :(
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Charith, try replacing "Sheet2" in L.Howards code with "Orders".
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514

ADVERTISEMENT

If the error was "Subscript out of range", then Teeroy's suggestion should fix it.

Howard
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Howard, there may be a sheet2 as well. If so you won't show an error but it's not acting as expected.
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514

ADVERTISEMENT

Good point.

If the OP still has problems, maybe Charith could give more explanation of what the snippet is or isn't doing. Error message or is a line highlighted yellow etc.

Howard
 

charith

Board Regular
Joined
Jan 3, 2014
Messages
152
i tried several time but it didn't work (no errors) :( :(

Actually what i want is if the order type is "SELL" or "BUY" Then " ORDERS" , "PRICE" and "% "columns should copy to the SHEET 2 columns B,C,D,F

anyway Thank you very much for your great help..
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Can you post a link to an example workbook along with what you want and where you want it?

I'm seeing sheets Main, Orders and Sheet 2 in your posts. Confused on where to copy what.

Howard
 

charith

Board Regular
Joined
Jan 3, 2014
Messages
152
Sir ,this is what i want .in the Sheet (Main) i have columns like this(below) , SO if the column "A" ="BUY" or "SELL" ,then all the data in column "A","D","E","F" (order type, order, price ,%) should copy to the Sheet(Orders) colmns "B","C","D",F

for a example

Sheet(Main) =>

if A1 = BUY

ORDER TYPE
#ORDERSPRICE%
BUY145.780.1

<tbody>
</tbody>



Then Sheet(Orders) =>
ABCDF
ADI BUY145.780.1

<tbody>
</tbody>
(here column A have company names )
___________________________________________________________


Sheet(main)
ABCDEF
ORDER TYPE#ORDERSPRICE%
BUY145.780.1
SELL232354.670.15
----
BUY34567890.23

<tbody>
</tbody>


Sheet(Orders)
ABCDFG
ADIBUY145.780.1
B2SELL232354.670.15
C1BUY34567890.23

<tbody>
</tbody>

Thank you very much
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,215
Members
417,131
Latest member
Seanr19871

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
Top