VBA - Copy and paste a range of values N times

[cydmm]

Hi everyone,
I have two dyanmic lists of codes:

1. In Column A a dynamic list of Retail Stores IDs
2. In Column C a dynamic list of Article Codes

What I am trying to do is to copy the used range of article codes (from C2 on) and paste it on the table in Range I1:J2 n times, with n equals to the number of CPCOs from Column A.
In other words I am trying to create a table that associates all the CARTs to all the CPCOs.

The best I succeeded to "code" is to copy and paste n times (Cell F1 value) all the CPCOs into the table.
Here it is the code:

Sub Repeatdata()

    Dim cell As Range
    Dim lr As Long
    Dim n As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
 
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    n = Range("F1").Value
    r = 2
 
    For Each cell In Range("A2:A" & lr)
        Range(Cells(r, "I"), Cells(r + n - 1, "I")) = cell.Value
        r = r + n
    Next cell
   
    Application.ScreenUpdating = True
 
End Sub

But how can I then copy and past all the CARTs for each copied CPCO?

Mark

 

[DanteAmor]

Try this:

Sub Repeatdata()
  Dim cell As Range
  Dim n As Long, r As Long
  Application.ScreenUpdating = False
  
  n = Range("F1").Value
  r = 2
  For Each cell In Range("A2", Range("A" & Rows.Count).End(3))
    Range("I" & r).Resize(n, 2).Value = Array(cell.Value, cell.Offset(0, 2).Value)
    r = r + n
  Next cell
  
  Application.ScreenUpdating = True
End Sub

 

[cydmm]

Hi DanteAmor,
thanks for your answer.
I run the code but it copies the CART Codes only the number of times indicated in cell F1, instead the number of times should be equal to the Number of CPCO codes.

 

[DanteAmor]

So I didn't understand your request.

Could you post an image with the expected result.

 

[cydmm]

This is what I would like to obtain.
Each one of the CART codes should be associated to each one of the CPCO Codes.
The two columns are dynamic.

Thank you and please apologize me if I have not been clear enough with my question.

 

[DanteAmor]

Try this:

Sub Repeatdata()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value
  b = Range("C2", Range("C" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a) * UBound(b), 1 To 2)
  
  For i = 1 To UBound(a)
    For j = 1 To UBound(b)
      k = k + 1
      c(k, 1) = a(i, 1)
      c(k, 2) = b(j, 1)
    Next
  Next
  Range("I2").Resize(UBound(c), 2).Value = c
End Sub

 

[cydmm]

Try this:

Sub Repeatdata()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value
  b = Range("C2", Range("C" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a) * UBound(b), 1 To 2)
 
  For i = 1 To UBound(a)
    For j = 1 To UBound(b)
      k = k + 1
      c(k, 1) = a(i, 1)
      c(k, 2) = b(j, 1)
    Next
  Next
  Range("I2").Resize(UBound(c), 2).Value = c
End Sub

It perfectly works ! Thank you so much Dante