Repeating same range of data

Akakumori

New Member
Joined
Jun 10, 2017
Messages
22
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!
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
You can put some examples of what you have in the 2 sheets and the expected result.
 

Akakumori

New Member
Joined
Jun 10, 2017
Messages
22
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

New Member
Joined
Jun 10, 2017
Messages
22
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
Try this, change sheets 1,2 y 3 for the names of yours sheets.

Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top