Prev: Help me create sales chart based on state and quantity
Next: Setting Scope of name ranges to particular worksheet
From: JPreeshl on 30 Apr 2010 21:38
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.
From: ozgrid.com on 30 Apr 2010 22:29
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" <JPreeshl(a)discussions.microsoft.com> wrote in message
news:19400664-D707-4A39-B191-282E5A5E43EE(a)microsoft.com...
>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.

From: Max on 30 Apr 2010 22:56
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.
 | 
Pages: 1
Prev: Help me create sales chart based on state and quantity
Next: Setting Scope of name ranges to particular worksheet