Macro to copy range data "x" number of times

[DevfromCorrie]

I have a workbook with four source data columns, Column A "Company", B "CC", C "EXP" and D "EXP Description. The expected output will be in sister columns of the same titles from F to I.
What I want to achieve is for each value in G to be copied "X" number of times based on the range of C & D and also populate Column H I with the copied EXP and EXP Description. The expected copied number for output column Company (Column F) will be count of Column C x count of column B

Apologies for my wording, its quite hard to explain. I have attached a spreadsheet highlighting in yellow the first two results I would expect.

[Marc L]

According to your attachment a starter demonstration :

Sub Demo1() 
    Dim L&, Rg(2) As Range, R& 
        L = 2 
        Set Rg(2) = Range("C2", [D1].End(xlDown)) 
        R = Rg(2).Rows.Count 
        [F1].CurrentRegion.Offset(1).Clear 
        Application.ScreenUpdating = False 
    For Each Rg(0) In Range("B2", [B1].End(xlDown)) 
        Rg(0).Copy Cells(L, 7).Resize(R) 
        L = L + R 
    Next 
        Set Rg(1) = Range("G2:G" & L - 1) 
        R = 2 
    For Each Rg(0) In Range("A2", [A1].End(xlDown)) 
        Rg(0).Copy Cells(R, 6).Resize(Rg(1).Count) 
        R = R + Rg(1).Count 
    Next 
        Rg(1).Copy Range(Cells(L, 7), Cells(R - 1, 7)) 
        Rg(2).Copy Range("H2:I" & R - 1) 
        Application.ScreenUpdating = True 
        Erase Rg 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Rick Rothstein]

How about we do this without using any loops...

Please Login or Register  to view this content.

[DevfromCorrie]

Hi Marc L, works perfectly and is also dynamic which is what is required. Many thanks for your quick resolution.