autofill a column with large larger intervals than the selected range

[trevhill]

I have a 30 cell descenting list in Tab 1 (my input tab)

ex:

john
terry
michael
ann
fred
etc.....


However, on another tab I'm trying to create a series in column 1, but with 84 rows in between each entry.

etc.

A1: john
a2: entry
a3; entry
a4: entry
a5:a84:entry

A85: terry
etc..........

Is there an easy way do do this?

 

[MickG]

Hi, Trty this:-

[COLOR="Blue"]Sub[/COLOR] MG30May55
[COLOR="Blue"]Dim[/COLOR] Rng, c [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long,[/COLOR] sert [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
 c = 1
 Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Blue"]For[/COLOR] sert = 1 To UBound(Rng)
        Sheets("sheet2").Cells(c, "A").Value = Rng(sert, 1)
        c = c + 84
    [COLOR="Blue"]Next[/COLOR] sert
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

Regards Mick

 

[Norie]

Something like this perhaps?

    Dim rngDst As Range
    Dim rngSrc As Range
    
    Set rngSrc = Worksheets(1).Range("A1")
    
    Set rngDst = Worksheets(2).Range("A1")
    
    While rngSrc.Value <> ""
        rngSrc.Copy rngDst
        
        Set rngSrc = rngSrc.Offset(1)
        Set rngDst = rngDst.Offset(84)
        
    Wend

 

[MickG]

Norie, Nice idea !!

 

[wsjackman]

if you would rather do it with a formula
in tab 2

select cells A1:A2550
enter
=IF(MOD(ROW(),85)=1,INDIRECT("'tab 1'!A"&INT(ROW()/85)+1),"")
confirm with control enter

copy
paste special values