Repeating same range of data

[Akakumori]

Hello,
How can I dos this?
I want to repeat a range of data which contains 3 colums and 70 rows 20 times.
First colum contains a reference number, same for all 70 rows, second column a vale which doesn't change and third colum the name of 70 different countries.
I want this range to repeat again below by just changing the reference number in the first row for another value I have listed un sheet2.
As many repetitions as references numbers I have un the list of sheet2.

Can someone help me? The task is huge when I have to paste same range like 20 or 50 times when there are lots of references.

Thanks!

 

[DanteAmor]

You can put some examples of what you have in the 2 sheets and the expected result.

 

[Akakumori]

Sheet1:

ColumnA/ColumnB/ColumnC
3564/666/France
3564/666/Italy
3564/666/Spain
....

Sheet 2:
ColumnA
3564
6437
7544
....

Hi
Let me know if it is understandable.
Thanks

 

[Akakumori]

Expected result:
ColumnA/ColumnB/ColumnC
3564/666/France
3564/666/Italy
3564/666/Spain
6437/666/France
6437/666/Italy
6437/666/Spain
7544/666/France
7544/666/Italy
7544/666/Spain

In same sheet1 or just un a new sheet, It doesn't matter.

Thanks

 

[DanteAmor]

Try this, change sheets 1,2 y 3 for the names of yours sheets.

Sub Repeating_range()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim i As Long, lr1 As Long, lr3 As Long
  Set sh1 = Sheets("[COLOR=#0000ff]Sheet1[/COLOR]")
  Set sh2 = Sheets("[COLOR=#0000ff]Sheet2[/COLOR]")
  Set sh3 = Sheets("[COLOR=#0000ff]Sheet3[/COLOR]")
  Application.ScreenUpdating = False
  sh3.Cells.ClearContents
  lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
  For i = 1 To sh2.Range("A" & Rows.Count).End(xlUp).Row
    sh1.Range("A1:C" & sh1.Range("A" & Rows.Count).End(xlUp).Row).Copy
    lr3 = sh3.Range("A" & Rows.Count).End(xlUp).Row
    sh3.Range("A" & lr3 + 1).PasteSpecial xlPasteAll
    sh3.Range("A" & lr3 + 1).Resize(lr1).Value = sh2.Range("A" & i)
  Next
  MsgBox "Done"
End Sub

 

[Akakumori]

Excellent, it works perfectly!!
Thanks that saves me a lot of time!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.

 

[Akakumori]

Hello,
I would like yo do a variation to this Macro and my source data will look like this:

sheet1:
ColumnA/ColumnB/ColumnC
1234/123/456
675/145/123
34/13/46
1346/567/678

Sheet2:
ColumnA
France
Japan
Greece

Expected result on sheet3:

Repeat data we have on sheet1 as many times as countries we have on sheet2:
ColumnA/ColumnB/ColumnC/ColumnD

1234/123/456/France
675/145/123/France
34/13/46/France
1346/567/678/France
1234/123/456/Japan
675/145/123/Japan
34/13/46/Japan
1346/567/678/Japan
....

In some cases ColumnC in sheet1 may be empty. In this case I want to keep it empty also in the final result in sheet3.

Thanks

 

[Akakumori]

Hello, any ideas how should I transform Macro provided by @DanteAmor in order to do this variation?
I have tried but my knowledge is limited and I can find a solution.

Thanks

 

[DanteAmor]

Try this

Sub Repeating_range()
  Dim sh1 As Worksheet, c As Range, lr1 As Long, lr3 As Long
  Set sh1 = Sheets("Sheet1")
  With Sheets("Sheet3")
    .Cells.ClearContents
    lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
    For Each c In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
      lr3 = .Range("A" & Rows.Count).End(xlUp).Row + 1
      .Range("A" & lr3).Resize(lr1, 3).Value = sh1.Range("A1:C" & lr1).Value
      .Range("D" & lr3).Resize(lr1).Value = c.Value
    Next
  End With
End Sub