Complex data transfer from one workbook to another

[philfriday]

Hi guys,

I have two workbooks (different files) that I need to feed information from one to another. The source has different funds (European Growth Portfolio, etc.) listed horizontally and products (Ultra 75/75 Series, etc.) listed vertically. The intersections contain add if they need to be fed.

Some sort of C#-ish VBA pseudocode of what I'm looking to achieve:
(source = source workbook, dest = destination workbook)

for (source!E1:H1) // Source Funds (European Growth...)
{
for (source!E2:E10) // Source Products (Ultra 75/75...)
{​
if (cell == "add") // Source Intersection​
{​
dest!A2 = CONCAT(source!A2, source!B2, source!D2); // Dest. Field Name
dest!B2 = source!E1; // Dest. AMG Fund Name
dest!C2 = source!C2; // Dest. AMG Code​
}​
}​
}

Tables to illustrate the situation:

Source workbook

Destination workbook (empty)

Destination workbook (after iteration)

I would really appreciate your help, guys! I'm not clear in my description please ask me. Thank you!

 

[Trevor_S]

Here is the code that you need. Replace Workbooks("Source").Activate and Workbooks("Dest").Activate with the actual workbook names (both need to be open). I've assumed that there are no blanks in row 1 or column A of the source workbook - i.e. once the macro hits a blank, its got to the last column/row of data. I've set screen updating to false while the macro is running to prevent flicker between the two workbooks (this should also help with speed).

Sub test()
Application.ScreenUpdating = False
myFirstSourceRow = 2
mySourceRow = myFirstSourceRow
myFirstSourceColumn = 5
mySourceColumn = myFirstSourceColumn
myDestRow = 2
Do
    Workbooks("Source").Activate
    If Cells(1, mySourceColumn).Value = "" Then
        mySourceRow = mySourceRow + 1
        If Cells(mySourceRow, 1).Value = "" Then Exit Do
        mySourceColumn = myFirstSourceColumn
    End If
    If Cells(mySourceRow, mySourceColumn).Value = "add" Then
        myFieldName = Cells(mySourceRow, 1).Value & " " & Cells(mySourceRow, 2).Value & " " & Cells(mySourceRow, 4).Value
        myFundName = Cells(1, mySourceColumn).Value
        myAMGCode = Cells(mySourceRow, 3).Value
        Workbooks("Dest").Activate
        Cells(myDestRow, 1).Value = myFieldName
        Cells(myDestRow, 2).Value = myFundName
        Cells(myDestRow, 3).Value = myAMGCode
        myDestRow = myDestRow + 1
    End If
    mySourceColumn = mySourceColumn + 1
Loop
Application.ScreenUpdating = True
msgbox("Finished")
End Sub

 

[philfriday]

Thanks very much, Trevor_S! This code does what it's intended to do. But I have a question: the macros is now listing all funds for a product, is it possible to list all products for a fund instead? What I mean is, I would want it to be grouped by funds, just like in the third screenshot. Thanks!

 

[Trevor_S]

Yes - just need to rewrite the macro to check down each column, rather than along each row! Try this:

Sub test()
Application.ScreenUpdating = False
myFirstSourceRow = 2
mySourceRow = myFirstSourceRow
myFirstSourceColumn = 5
mySourceColumn = myFirstSourceColumn
myDestRow = 2
Do
    Workbooks("Source").Activate
    If Cells(mySourceRow, 1).Value = "" Then
        mySourceColumn = mySourceColumn + 1
        If Cells(1, mySourceColumn).Value = "" Then Exit Do
        mySourceRow = myFirstSourceRow
    End If
    If Cells(mySourceRow, mySourceColumn).Value = "add" Then
        myFieldName = Cells(mySourceRow, 1).Value & " " & Cells(mySourceRow, 2).Value & " " & Cells(mySourceRow, 4).Value
        myFundName = Cells(1, mySourceColumn).Value
        myAMGCode = Cells(mySourceRow, 3).Value
        Workbooks("Dest").Activate
        Cells(myDestRow, 1).Value = myFieldName
        Cells(myDestRow, 2).Value = myFundName
        Cells(myDestRow, 3).Value = myAMGCode
        myDestRow = myDestRow + 1
    End If
    mySourceRow = mySourceRow + 1
Loop
Application.ScreenUpdating = True
msgbox("Finished")
End Sub