Excel VBA:Paste multiple times

[go2nivas]

Hi All,

I am new to Excel Macro and to this forum. I am trying to write a macro to copy a range multiple times. Please correct me if I am wrong.

Sub Macro1()
Dim x As String
Dim x1 As String
Dim x2 As String

Dim p, q As Integer
Range("A1").Activate

p = 1
q = 1
For i = 1 To 2 Step 1
x2 = Trim(Cells(i, 1).Value)
x = Trim(Cells(i, 2).Value)
p = 1
For j = 1 To c Step 1
q = InStr(p, x)
x1 = Mid(x, p, q - p)
ActiveCell.Value = x2
ActiveCell.Offset(0, 1).Value = x1
ActiveCell.Offset(1, 0).Activate
p = q + 1
Next j

Next i

End Sub

Please see the below-attached image example,

Input

Output

Thank you in advance.

 

[Fluff]

How about

Sub go2nivas()
   Dim Rng As Range
   Dim i As Long, j As Long
   
   j = 1
   Set Rng = Range("B1", Range("B" & Rows.Count).End(xlUp))
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Copy Range("D1").Resize(.Count * Rng.Count)
      For i = 1 To Rng.Count
         .Range("E" & j).Resize(.Count).Value = Rng(i)
         j = j + .Count
      Next i
   End With
End Sub

 

[go2nivas]

Wow! that was quick.

It works great. Thank you so much @Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.