VBA Copying Set of Data n Times

[elgre]

Hello everyone,

I have a question regarding copying set of data n times. I hope it is not too complicated and you could kindly help.

As I tried to illustrate an example with my perfectly artistic sketch, I want to copy the cells in column A to Column G for (lets say) 12 times, until data in column A finishes. Then I want to copy the set of data in column B (from AA to LL) to column H for each group (12times copied cells). I also tried to show it in the table below.

I would be really appreciated if you could show me the way to do this by using VBA.

Thank you very much for your help in advance.

Header1 - Data 1	Header2 - Data 2	Header 3	Header4
A	AA	A	AA
B	BB	A	BB
C	CC	A	CC
D	DD	A	DD
E	EE	A	EE
F	FF	A	FF
...	GG	A	GG
	HH	A	HH
	II	A	II
	JJ	A	JJ
	KK	A	KK
	LL	A	LL
	(no more data after LL)	B	AA
		B	BB
		B	CC
		B	DD
		B	EE
		B	FF
		B	GG
		B	HH
		B	II
		B	JJ
		B	KK
		B	LL
		(continue until data finishes in Data1)	(continue until data finishes in Data1)

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub elgre()
   Dim Rng As Range, Cl As Range
   
   Set Rng = Range("B2", Range("B" & Rows.count).End(xlUp))
   For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
      With Range("G" & Rows.count).End(xlUp).Resize(Rng.count)
         Cl.Copy .Offset(1)
         .Offset(1, 1).Value = Rng.Value
      End With
   Next Cl
End Sub

 

[elgre]

That is amazing! Thanks a lot for your help, perfectly works!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[doremi]

Hi & welcome to MrExcel.
How about

Sub elgre()
   Dim Rng As Range, Cl As Range
  
   Set Rng = Range("B2", Range("B" & Rows.count).End(xlUp))
   For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
      With Range("G" & Rows.count).End(xlUp).Resize(Rng.count)
         Cl.Copy .Offset(1)
         .Offset(1, 1).Value = Rng.Value
      End With
   Next Cl
End Sub

Hello everyone, how I can change this VBA to copy two columns instead of one? Lets say the B column is actually C column and there is one extra column - for the purpose of explanation I will add one more column before A. Now - both columns are with the same number of rows (A and B) instead of the one column which is in the example above. I need to copy both of them. I tried to change the macro but without success. Your help is greatly appreciated.

 

[Fluff]

As your question is significantly different from the op, please start a thread of your own. Thanks