CODEWITHSUNDEEP
excelvba
MikeS
MikeS

Reputation: 13

How can I copy each value and past it multiple times using Excel VBA?

I have Sheet1:
| A |
| B |
| C |

How can I copy values from Sheet1 to Sheet2 so I will get this?

| A |
| A |
| A |
| B |
| B |
| B |
| C |
| C |
| C |

...just copy each value from Sheet1 x times to Sheet2 using Excel VBA. I did find some solutions but too complex or too simple for my needs, like this one (I dont understand what "eRow" is):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
x = 2
Do While Cells(x, 1) <> ""
Worksheets("Sheet4").Rows(x).Copy
Worksheets("Sheet3").Activate
eRow = ActiveSheet.Cell(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet3").Rows(eRow)
Worksheets("Sheet4").Activate
x = x + 1
Loop
End Sub

Thank you.

Upvotes: 0

Views: 123

Answers (1)

JNevill
JNevill

Reputation: 50019

VBA would be helpful here, but I think your best bet is to do this with worksheet formulas. Using INDIRECT(), which allows you to change a string into a cell range, CEILING() which is like rounding up to the nearest integer, and ROW() to get the row number of the cell in which the formula resides, you could do:

=INDIRECT("Sheet1!A" & CEILING(ROW()/3,1))

Take that and copy it down Sheet2 starting at A1

If you want it so it makes 4 copies of each cell from Sheet1 then change that 3 to a 4 and you'll be set.

Upvotes: 2

Related Questions