# copying values and pasting the same value on multiple cells

1. ## copying values and pasting the same value on multiple cells

im having a hard time with this one. im trying to copy data from one sheet to another. what i want to happen is that i paste 7 copies of that data on the second sheet. i have a lot of data to work with and paste 7 times in a different sheet. i was just wondering if this could be done by using a formula.

to get a better visual on what i want to happen -

SHEET 1

Column A
1
2
3

SHEET 2

Column A
1
1
1
1
1
1
1
2
2
2
2
2
2
2
3
3
3
3
3
3
3

I hope you can help me with this one.

Thanks!

2. Copy data from sheet1 to sheet2
In sheet2 col B put serial numbers against your data in col A (i-e 1, 2, 3... downward).

copy serial # in col B and paste 7 times in col B at first blank cell. e.g if you have data in col A in range A1:A50, put serial # in col B in range B1:B50 then copy B1:B50 and paste in B51 then in B101 and onward for 7 times.

Select both columns A & B till last serial # in col B then go to Data > Sort > and sort Ascending on col B. (you will see 7 blank cells in col A under each value of data).

Now select col A till last value plus 7 cells, press F5 and hit Special tab and select Blanks from options and press Ok (all blank cells will be selected).
Press = and then hit up arrow only once and press Ctrl+Enter.

Copy col A and right click > Paste special, check Values, press Ok.
Delete col B.

Hope this will do what you require.

3. Originally Posted by starguy
Copy data from sheet1 to sheet2
In sheet2 col B put serial numbers against your data in col A (i-e 1, 2, 3... downward).

copy serial # in col B and paste 7 times in col B at first blank cell. e.g if you have data in col A in range A1:A50, put serial # in col B in range B1:B50 then copy B1:B50 and paste in B51 then in B101 and onward for 7 times.

Select both columns A & B till last serial # in col B then go to Data > Sort > and sort Ascending on col B. (you will see 7 blank cells in col A under each value of data).

Now select col A till last value plus 7 cells, press F5 and hit Special tab and select Blanks from options and press Ok (all blank cells will be selected).
Press = and then hit up arrow only once and press Ctrl+Enter.

Copy col A and right click > Paste special, check Values, press Ok.
Delete col B.

Hope this will do what you require.
Hi Starguy,

Thanks a lot!! :-) it worked!

4. Originally Posted by okidonkey
Hi Starguy,

Thanks a lot!! :-) it worked!
Good to see that it worked and your problem is solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1