Copy data and paste it into every second row

[rhibrown]

I would like to be able to copy my data from Column T, and then paste it into another column, but into every second row.

So if I had these values in column T:
2
4
5
6
7

I would like to copy and paste it into another column to show:
2

4

5

6

7

I'd also like another part of the code to copy the values and duplicate them, e.g.
2
2
4
4
5
5
6
6
7
7

Any ideas?

 

[vletm]

rhibrown
As You would like to do ...
1) Have Your values in column T as in Your sample
2) Select cell from which You would like to get 'results'
3) Press [ Do It ]

 

[Marc L]

Any ideas?

Not following this forum rules when posting an incomplete explanation for your data layout (source & destination addresses)​

neither any workbook attachment so that means you are very confident enough with your VBA skills​

in order to amend any helper demonstration to well fit your need !​

​

As here that's just Excel basics with a beginner level formula, if needed the mod is easy so you can do it …​

​

For 'every second row' :​

Sub Demo1()
         Const F = "IF(ISODD(ROW(#)),INT(ROW(#)/2)+1,¤)"
    With Range("T1", Cells(Rows.Count, 20).End(xlUp)(2)).Rows
         V = Evaluate(Replace(Replace(F, "#", .Resize(.Count * 2 - 3).Address), "¤", .Count))
        .Offset(, 2).Resize(UBound(V)).Value2 = Application.Index(.Value2, V)
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

To duplicate values :​

Sub Demo2()
    With Range("T1", Cells(Rows.Count, 20).End(xlUp)).Rows
     .Offset(, 4).Resize(.Count * 2).Value2 = Application.Index(.Value2, Evaluate("IF({1},INT((ROW(" & .Resize(.Count * 2).Address & ")+1)/2))"))
    End With
End Sub

You like ? Thanks to click on bottom right Like !​

​

 

[rhibrown]

To duplicate values :​

Sub Demo2()
    With Range("T1", Cells(Rows.Count, 20).End(xlUp)).Rows
     .Offset(, 4).Resize(.Count * 2).Value2 = Application.Index(.Value2, Evaluate("IF({1},INT((ROW(" & .Resize(.Count * 2).Address & ")+1)/2))"))
    End With
End Sub

You like ? Thanks to click on bottom right Like !​

​

Thanks!
In the code, what does 'value2' mean?

 

[Marc L]

Means nothing, it's just a property as you can read in the VBA help …​

​

 

[rhibrown]

Okay thanks.

If i want to change the destination from Offset by 4 columns, and I want it to go onto another worksheet called "Template" at L39, how would i adjust it?

 

[Marc L]

Replace .Offset(, 4) by the destination …​

​

 

[rhibrown]

rhibrown
As You would like to do ...
1) Have Your values in column T as in Your sample
2) Select cell from which You would like to get 'results'
3) Press [ Do It ]

Thanks! This is great! I've managed to adapt it so I can put it where ever I want on that worksheet, but
I've tried to adapt it so I can put the values on another worksheet called 'Template' starting at L39, but I'm having trouble as the data is on worksheet ' Workings' at G7... how do I adjust?

    With ActiveSheet
        ay = Worksheets("Template").Range("L39").Row
        ax = Worksheets("Template").Range("L39").Column
        t = 2
        Do
            .Cells(ay + 0, ax) = .Cells(t, "G")
            .Cells(ay + 1, ax) = .Cells(t, "G")
              t = t + 5
            DoEvents
        Loop Until .Cells(t, "G") = Empty
    End With

 

[vletm]

rhibrown
Your new 'idea' is totally different!
eg this way ...

 

[rhibrown]

rhibrown
Your new 'idea' is totally different!
eg this way ...

Thank you! That is exactly what I need. Are you able to explain the code please?

Another question - is there a way I can alter that to make it so that instead of duplicating, it leaves a blank row between row of data?
So if my data was
1516.001
1516.003
1516.004

I would want it:
1516.001

1516.003

1516.004

Again, thank you so much for your help! I greatly appreciate it!

 

[vletm]

rhibrown
#1) Did You check that 'code'?
there is a loop
... copy value from sheet1 place yx to sheet2 yyxx
... copy same value also to next row
until no more values to copy
#2) hide that 2nd copy from code