VBA - Help with Copy/Paste from Source Workbook to Create Multiple Rows in Active Workbook

[Luannilow]

I'm new to VBA and need some help. I'm looking for most efficient way to create an import file based off of an Excel Spreadsheet.

Source Workbook contains a variable number of rows with 7 columns of data.
In my active workbook, I need to create 5 separate rows for EACH line of data in the Source Workbook.
In the active workbook, the letter or number in column A will always be hard-coded for all 5 rows (see example below).
In the active workbook, the values in the 2nd column & 3-5 rows will also be hard-coded (see example below).

I need to loop through this routine for as many rows that are on the Source data sheet, which varies from month-to-month.

Source Workbook

1	A	10	BB	40.00	20.00	20.00
2	A	20	ED	55.00	35.00	20.00
3	C	50	AA	100.00	75.00	25.00

<tbody>
</tbody>

Active Workbook

0	1	BB
1	A
U	29	40.00
U	81	20.00
U	82	20.00
0	2	ED
1	A
U	29	55.00
U	81	35.00
U	82	20.00
0	3	AA
1	C
U	29	100.00
U	81	50.00
U	82	50.00

<tbody>
</tbody>

Thanks in advance for any help you can throw my way. I tried recording this as a macro but I can't get the macro to keep looping through for each line of data.

 

[Nishant94]

Sub ConverData()


    Dim Arr As Variant
    Dim SrcWb As Workbook
    Dim MyActiveWb As Workbook
    Dim x, y, i As Integer
    
    Set MyActiveWb = ActiveWorkbook
        
    'Copy the path of the source workbook in the area marked with color red
    Set SrcWb = Workbooks.Open("[COLOR=#ff0000]C:\Users\Nishant\Desktop\Source Workbook.xlsm[/COLOR]")
    
    'Check the sheet name in the source workbook and the range. Adjust it according to your need
    Arr = SrcWb.Sheets("[COLOR=#ff0000]Source[/COLOR]").Range("[COLOR=#ff0000]A1:G3[/COLOR]").Value
    
    For i = 1 To UBound(Arr, 1)
        
        For x = 1 To UBound(Arr, 2)
            
            y = y + 1
    'Adjust the name of sheet in the activeworkbook according to your need
            With MyActiveWb.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
            
                If x = 1 Then
                    .Cells(y, 1) = 0
                    .Cells(y, 2) = Arr(i, 1)
                    .Cells(y, 3) = Arr(i, 4)
                ElseIf x = 2 Then
                    .Cells(y, 1) = 1
                    .Cells(y, 2) = Arr(i, x)
                ElseIf x = 3 Then
                    .Cells(y, 1) = "U"
                    .Cells(y, 2) = 29
                    .Cells(y, 3) = Arr(i, 5)
                ElseIf x = 4 Then
                    .Cells(y, 1) = "U"
                    .Cells(y, 2) = 81
                    .Cells(y, 3) = Arr(i, 6)
                ElseIf x = 5 Then
                    .Cells(y, 1) = "U"
                    .Cells(y, 2) = 82
                    .Cells(y, 3) = Arr(i, 7)
                End If
                
            End With
            
        Next x
        
        y = y - 2
        
    Next i
    
End Sub

 

[Luannilow]

Nishant, WOW! This is awesome. I'd seen the array commands but just couldn't put it all together in my head. Thanks for giving me a great, working example of exactly what I was trying to pull off. Just awesome!!!