CODEWITHSUNDEEP
excelvbaexcel-formula
LovetoLearn
LovetoLearn

Reputation: 41

Excel Copy and Pasting data into multiple cells

I have an Excel question that comes in two parts: Firstly, if I would like to copy data from one worksheet to another where Sheet 1 has:

A
B
C
D
E

and for Sheet 2 I want every cell to repeat thrice such that when I paste the previous five cells into Sheet 2, each cell appears thrice:

A
A
A
B
B
B
C
C   
C
D
D
D
E
E
E

How do I do that? I personally do now know of any formula or function that can let me do that so really looking forward to your advice.

Once that is done, is there a way to write it up using VBA? I am very very new to VBA and was just thinking if it is possible. Otherwise, I will just record a macro. Thank you very much!

Upvotes: 0

Views: 791

Answers (3)

LovetoLearn
LovetoLearn

Reputation: 41

Eventually, I did the old-fashioned way of doing it step by step and it worked:

LastRow = have.Cells(have.Rows.Count, "A").End(xlUp).Row
Dim p As Long
pp = 3
For p = 1 To LastRow
    want.Range("A" & pp) = have.Range("A" & p).Value
    pp = pp + 1
    want.Range("A" & pp) = have.Range("A" & p).Value
    pp = pp + 1
    want.Range("A" & pp) = have.Range("A" & p).Value
    pp = pp + 1
Next p

Upvotes: 0

user3598756
user3598756

Reputation: 29421

a VBA way:

Option Explicit

Sub main()
    Dim data As Variant, datum As Variant
    Dim iDatum As Long, nTimes As Long

    With Worksheets("Sheet 1") '<--| reference your "source" worksheet (change "Sheet 1" to your actual "source" sheet name
        data = Application.Transpose(.Range("A1", .Cells(.Rows.count, 1).End(xlUp)).Value) '<--| store its column A cells values from row 1 down to last not empty one into an array
    End With

    nTimes = 3 '<--| set how many times you want to copy the same value
    With Worksheets("Sheet 2") '<--| reference your "target" worksheet (change "Sheet 2" to your actual "target" sheet name
        .Range("A1").Resize(nTimes) = data(LBound(data)) '<--| write 1st 'data' array value 'nTimes' from its cell A1 down
        For iDatum = LBound(data) + 1 To UBound(data) '<--| loop through other 'data' array values
            .Cells(.Rows.count, 1).End(xlUp).Offset(1).Resize(nTimes) = data(iDatum) '<--| write them 'nTimes' from first empty row after last not empty one down
        Next iDatum
    End With
End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

With data in Sheet1 like:

enter image description here

In Sheet2, cell A1 enter:

=INDEX(Sheet1!A:A,ROUNDUP(ROW()/3,0))

and copy down:

enter image description here

( if you want 4 copies of each data item, use 4 in the formula)

Upvotes: 4

Related Questions