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
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]BB[/TD]
[TD]40.00[/TD]
[TD]20.00[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]20[/TD]
[TD]ED[/TD]
[TD]55.00[/TD]
[TD]35.00[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]50[/TD]
[TD]AA[/TD]
[TD]100.00[/TD]
[TD]75.00[/TD]
[TD]25.00[/TD]
[/TR]
</tbody>[/TABLE]

Active Workbook
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]0[/TD]
[TD]1[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]29[/TD]
[TD]40.00[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]81[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]82[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2[/TD]
[TD]ED[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]29[/TD]
[TD]55.00[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]81[/TD]
[TD]35.00[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]82[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]3[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]29[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]81[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]82[/TD]
[TD]50.00[/TD]
[/TR]
</tbody>[/TABLE]

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!!!