copy offset rows

  • Thread starter Thread starter JPreeshl
  • Start date Start date
J

JPreeshl

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.
 
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
 
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
 
Back
Top