For Each Loop Macro

I have included a sample file with a basic example of an input and the desired output after the macro has ran. This list will be dynamic on the input tab Col A and Col C will only be around 4-5 lines long but I would want the macro to be able to handle more if needed in the future. Col B however will range from 1-75,000+ again would want it to be able to handle any size.

The basic idea is that the info in Col A&C will be duplicated for each entry in Col B. The data in Col A&C are paired so as in the example A will always be with X, B always with W, and C always with Z.

I get the feeling this can be done with a for each loop but i am having "writer's block"/"brain fart" in figuring out how to actually code it.

I am using Excel 2007 on Windows XP if you need that info.

All help is appreciated and please let me know if there is any more info you need.

Sample file.xlsx

Hello chad portman,

The macro below has been added to the the attached workbook. There is a button on the "Desired Output" to run it. It uses 2 For Each Next loops to create the "Desired Output".

While this macro works with your sample data, it may not with your actual data unless the following conditions apply...

1. There are no column headers used.
2. There is at least one blank row and column around the input and output data areas

Macro Code

Sub Macro1()

    Dim DstRng  As Range
    Dim CellA   As Range
    Dim CellB   As Range
    Dim SrcRng  As Range
    
        Set SrcRng = Sheet1.Range("A1").CurrentRegion
        Set DstRng = Sheet2.Range("A1:C1")
        
        DstRng.CurrentRegion.ClearContents
        
            For Each CellA In SrcRng.Columns(1).Cells
            
                For Each CellB In SrcRng.Columns(2).Cells
                
                    If Not IsEmpty(CellA) Then
                        DstRng.Value = Array(CellA, CellB, CellA.Offset(0, 2))
                        Set DstRng = DstRng.Offset(1, 0)
                    End If
                    
                Next CellB
                
            Next CellA
            
End Sub