| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy once and paste five times
Hi
I have a column of 172 text values in Sheet2. I want to paste each value five times in the first column of Sheet1. For example: -------------- Sheet2: Column A Biscuits Jam Peanut Chocolate .... -------------- Sheet1: Column B Biscuits Biscuits Biscuits Biscuits Biscuits Jam Jam Jam Jam Jam Peanut Peanut Peanut Peanut Peanut Chocolate Chocolate Chocolate Chocolate Chocolate .... So far I have coded the following: Sub Macro1() Dim i Dim j For i = 1 To Sheet2.UsedRange.Rows.Count For j = 1 To 5 Sheets(1).Range("A" & j) = Sheets(2).Range("A" & i).Value Next Next End Sub As you can see, the above would simply paste the values over and over again on the first five cells. I am not sure how to get around this problem. I would be grateful for any help. Regards Yousaf |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy once and paste five times
Sheets(1).Range("A" & (j + (i - 1) * 5)) = Sheets(2).Range("A" & i).Value
This is all on one line, of course "Zuzeppeddu" wrote in message ps.com... Hi I have a column of 172 text values in Sheet2. I want to paste each value five times in the first column of Sheet1. For example: -------------- Sheet2: Column A Biscuits Jam Peanut Chocolate ... -------------- Sheet1: Column B Biscuits Biscuits Biscuits Biscuits Biscuits Jam Jam Jam Jam Jam Peanut Peanut Peanut Peanut Peanut Chocolate Chocolate Chocolate Chocolate Chocolate ... So far I have coded the following: Sub Macro1() Dim i Dim j For i = 1 To Sheet2.UsedRange.Rows.Count For j = 1 To 5 Sheets(1).Range("A" & j) = Sheets(2).Range("A" & i).Value Next Next End Sub As you can see, the above would simply paste the values over and over again on the first five cells. I am not sure how to get around this problem. I would be grateful for any help. Regards Yousaf |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy once and paste five times
Thanks. Works perfectly.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to paste x number of times | Excel Discussion (Misc queries) | |||
| copy and paste 2900 times macro | Excel Discussion (Misc queries) | |||
| Copy & paste block of text number of times equal to value in cell | Excel Worksheet Functions | |||
| Macro - copy a range and paste it in a new sheet 12 times | Excel Worksheet Functions | |||
| Select cells copy/paste and repeat x times | Excel Programming | |||
Linear Mode
