# Copy abd paste

#### abeed

##### Board Regular
Hi: I am selecting data from one sheet and pasting into another. In sheet1, I have data in one row where I will go 12 times to take data from that row. Each time I will copy 12 cells. In sheet2, I will paste these data in row to row. I mean, each time I will copy 12 cells from sheet1 and paste them in sheet2 in different rows. Without going(or activating) each time back and forth(sheet1 and sheet2), how can I do this copy and paste in a different way???

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi

something like

sheets("sheet2").range("a1").value = sheets("sheet1").range("a1").value

Update the from and to references as required.

Tony

Hi Tony:

Thanks a lot. Do I have to write the code then 12 times if I copy and paste 12 times. This is what I want to avoid. I have to copy 12 times from single row and I have 15 rows to copy from. Thus I have to write the code 12x 15 times(as the range will be changing).

Code:
``````For x = 1 to 15

sheets("sheet2").range("a" & x).value = sheets("sheet1").range("a" & x).value

Next x``````

You can nest the line of code in a loop as above. Change 1 and 15 to the rows you need .

Is the data being copied contiguous and the output range also contiguous? If so, then you can do the entire range in one copy

Sheets("sheet2").Range("a1:a12").Value = Sheets("sheet1").Range("a1:a12").Value

If the input and output cells are not contiguous, then show the input / output mappings for us to review.

Tony

Hi Tony: Thanks. In my copy area, I have last 2 rows a bit different. These 2 rows dont have data for the first 3x12(=36) cells. That means even if I put my code into a loop, what will happen with the last 2 rows. I will loop through here also 12 times though first 3 times(=36 cells) dont have any data and data starts from 37th cell.

Other rows have data in each row for 12 times to copy from. only the last 2 rows are differnt.

Hi

can you please give an example of your data using cell references so we can see where it is coming from and where it is going.

Tony

The input sheet1,say has 7 rows with data. Each time I have to go a row and copy 12 cells from that row. Total data cells in each row 144 and contiguous. The last 2 rows also end as other rows. But they dont have data till 37th cell. The paste are is a blank sheet2. I will start at cell(2,2) and will paste 1st cpied 12 cells from sheet1.row1. Then move to cell(3,2) and paste there the 2nd 12 cells copied from sheet1.row1 and so on.

copy sheet1:
r1: 10 20 5 40 50 10 70 80 90......55(cell144th)--contiguous
r2: 20 10 20 10 ............................25(cell144th)---conti
..
r6: ..................30(37thcell),30......30(cell144th)--data starts at 37th cell
r7:...........................50(42thcell)...40(cell144th)--data starts at 42nd cell
Paste shet2:
1990.(2,1)..........................
1991...................................
1992...................................

Its a blank sheet and I will start at (2,1) ,(3,1) and so on.

In copy sheet1, say 1st 5 rows have 144 cells with data and last 2 rows start at cell37th and at cell42nd. So Can I still loop through. I need something to check if the range dont have any data then dont copy. Say in last row I will loop 12 times, but each time I will check if the range has data only then copy.
Thanks

Hi

Using the example above, rows 1:5 have data completely from column A to column EN. However, rows 6 and 7 the data starts in AK and AP respectively and goes to EN.

With your data, will there only be blank cells leading up to the first completed column and will they then always be contiguous until column EN? So with the example, because there is data is column A for the first 5 rows, you can copy from column A to column EN and paste it out. However for row 6, if you start in column A, then (manually) go ctrl, right arrow you will get to AK. Can you assume that there will always be data from that point on to EN??

Tony

Hi tony:
Yes. For rows 6 & 7, there will be conguous data from(say) AK ,AP to EN. Tony, when I copy (by 12 cells) cannot I check whether the range is full blank without any data. Only then I will take that range, copy it and then paste to the other sheet. This way, I can use a single loop of row=7 and for each row I will select a range of 12 cells for 12 times(12x12).
Anyway, all 7 row datas end at same column but 6,7 starts at different point.
Thanks

Replies
2
Views
246
Replies
5
Views
151
Replies
6
Views
298
Replies
9
Views
200
Replies
10
Views
920

1,196,504
Messages
6,015,587
Members
441,903
Latest member
MG12345

### 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.

### Which adblocker are you using?

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

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