Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy offset rows
I have a column of names in consecutive rows. I want to copy them to another
sheet where the 1st name is in row 5 then the 2nd name is in row 15 etc with an increase of 10 rows between the copied names. If I try to copy a formula it just takes every 10th name not the next one. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy offset rows
Try;
Sub CopyNames() Dim rCell As Range Dim strFirst As String Dim strSecond As String Dim lFirst As Long Dim lSecond As Long Dim ws As Worksheet 'CodeName 'http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm Set ws = Sheet2 lFirst = 5 lSecond = 15 For Each rCell In Range("A2", Cells(Rows.Count, 1).End(xlUp)) strFirst = Split(rCell)(0) strSecond = Split(rCell)(1) With ws .Cells(lFirst, 1) = strFirst .Cells(lSecond, 1) = strSecond End With lFirst = lSecond + 5 lSecond = lFirst + 10 Next rCell End Sub -- Regards Dave Hawley www.ozgrid.com "JPreeshl" wrote in message ... I have a column of names in consecutive rows. I want to copy them to another sheet where the 1st name is in row 5 then the 2nd name is in row 15 etc with an increase of 10 rows between the copied names. If I try to copy a formula it just takes every 10th name not the next one. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy offset rows
Here's an easy formulas way to deliver it ..
Assume your source data (names) is in Sheet1, running consecutively in A2 down In another sheet, put this in the starting cell of your choice, say in A5: =IF(MOD(ROWS($1:1)-1,10)=0,OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/10),),"") Copy down as far as required to exhaust the extract of names. You'd get the 1st name in A5, the 2nd name in A15, 3rd in A25 and so on. In between, the formula will return "blanks". Exactly as desired. Easily adapt the interval: 10 in both the MOD and INT parts to suit other intervals. Change/point the OFFSET's anchor, ie: Sheet1!$A$2 to suit where your actually source data starts. Inspiring? hit the YES below -- Max Singapore --- "JPreeshl" wrote: I have a column of names in consecutive rows. I want to copy them to another sheet where the 1st name is in row 5 then the 2nd name is in row 15 etc with an increase of 10 rows between the copied names. If I try to copy a formula it just takes every 10th name not the next one. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost: Copy and Offset cell reference | Excel Worksheet Functions | |||
Copy link to offset sheet | Excel Worksheet Functions | |||
Offset every # of Rows | Excel Worksheet Functions | |||
copy offset to cell | Excel Discussion (Misc queries) | |||
copy and paste with offset | Excel Discussion (Misc queries) |