Populating 1 row in workbook B from 2 alternating rows in workbook A

[Help9182]

Hello all,

Does anyone know how to auto populate 1 row in excel (Workbook B) from information contained in 2 rows in a separate spreadsheet (workbook A)?


Workbook A auto populates daily from a google doc form and workbook B is used for accounting (inputted manually).
Do you know how to make workbook A auto populate workbook B?


Workbook A (2 new rows each day)
Date + time | venue A | no. 1 | no. 2 | no.3 | no. 4
Date + time | venue B | no. 1 | no. 2 | no.3 | no. 4


Workbook B (1 new row each day)
Date | no. 1 (venue A) | no. 3 (Venue A) | no. 4 (Venue B) | no. 2 (Venue B)

Any help would be greatly appreciated.

Please let me know if you have any questions.

Many thanks!

 

[DanteAmor]

Try this

Put the macro in book B.
Change the name of the workbook file A

Sub Populating()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set wb1 = ThisWorkbook  'book B
    Set sh1 = wb1.Sheets(1)
    
    Set wb2 = Workbooks("[COLOR=#ff0000]workbook A.xlsx[/COLOR]")  'book A
    Set sh2 = wb2.Sheets(1)
    
    lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row + 1
    lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
    
    sh1.Range("A" & lr1).Value = sh2.Range("A" & lr2 - 1).Value
    sh1.Range("B" & lr1).Value = sh2.Range("C" & lr2 - 1).Value
    sh1.Range("C" & lr1).Value = sh2.Range("E" & lr2 - 1).Value
    sh1.Range("D" & lr1).Value = sh2.Range("F" & lr2).Value
    sh1.Range("E" & lr1).Value = sh2.Range("D" & lr2).Value
    
    MsgBox "Done"
End Sub