Copy abd paste

[abeed]

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

Thanks in advance

 

[acw]

Hi

something like

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

Update the from and to references as required.


Tony

 

[abeed]

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

 

[XLGibbs]

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 .

 

[acw]

ADVERTISEMENT

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

 

[abeed]

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.

 

[acw]

ADVERTISEMENT

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

 

[abeed]

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

 

[acw]

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

 

[abeed]

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