Vba to copy Dynamic rows data from w/book to another w/book

[tkraju]

Source w/book1 sheet1 has variable data from A2:E. Source w/book 2 sheet1 also has variable data from A2:E. How to write VBA code to copy this data and paste Destination w/book Sheet1 in the last row in columns B:F(source w/book 1 data) and in columns H:L (source w/book 2 data). File paths are same for all 3 w/books. Vba to run from Destination w/book.

 

[OaklandJim]

I hope that you can use this code. If not, I can provide workbooks.

Option Explicit

Sub AggregateData()

'   Data source 1 workbook.
    Dim wbDataSource1 As Workbook

'   Data source 2 workbook.
    Dim wbDataSource2 As Workbook

'   Target worksheet where data is pasted
    Dim wsTargetSheet As Worksheet
    
'   Last occupied row in the target worksheet.
    Dim iLastRowTarget As Long
    
'   Last occupied row in the source worksheet.
    Dim iLastRowSource As Long
    
'   The name of the source 1 workbook without file extension.
    Dim sWB1Name As String
    
'   The name of the source 2 workbook without file extension.
    Dim sWB2Name As String
    
    Dim sFileExtention As String
    
'   Used for message for the user.
    Dim sMsg As String
    
    Dim bDoMacroEnabledWB As Boolean
    
'   Close the data workbooks after getting data?
    Dim bCloseAfter As Boolean
    
'   The name of the source 1 workbook without file extension.
    sWB1Name = "SourceData1" '<= change this to be the name of data source workbook 2

'   The name of the source 2 workbook without file extension.
    sWB2Name = "SourceData2" '<= change this to be the name of data source workbook 2
    
'   Workbooks are macro enabled or just
    bDoMacroEnabledWB = True
    
'   Close the data workbooks after getting data?
    bCloseAfter = True
    
    If bDoMacroEnabledWB _
     Then
        sFileExtention = ".xlsm"
    Else
        sFileExtention = ".xlsx"
    End If
    
    sMsg = ""
    
'   ----------------------------------
'       Open Source Data Workbooks
'   ----------------------------------
    On Error Resume Next
    Set wbDataSource1 = Workbooks(sWB1Name)

    If wbDataSource1 Is Nothing _
     Then
        Set wbDataSource1 = Workbooks.Open(ThisWorkbook.Path & "\" & sWB1Name & sFileExtention)
    End If
    On Error GoTo 0

    On Error Resume Next
    Set wbDataSource2 = Workbooks(sWB2Name)

    If wbDataSource2 Is Nothing _
     Then
        Set wbDataSource2 = Workbooks.Open(ThisWorkbook.Path & "\" & sWB2Name & sFileExtention)
    End If
    On Error GoTo 0
    
    If wbDataSource1 Is Nothing _
     Then
        sMsg = "Workbook " & sWB1Name & " was not found."
    ElseIf wbDataSource2 Is Nothing _
     Then
        sMsg = "Workbook " & sWB2Name & " was not found."
    End If

    If Not sMsg = "" _
     Then
        MsgBox sMsg
        Exit Sub
    End If
    
'   ----------------------------
'       Process Data Sheets
'   ----------------------------

'   Set target worksheet in target workbook.
    Set wsTargetSheet = ThisWorkbook.Worksheets("Sheet1")
    
'   ~~~~~ Data Sheet 1 ~~~~~
    
'   Last row in column B in the target worksheet.
    iLastRowTarget = wsTargetSheet.Range("B1").Cells(Rows.Count, 1).End(xlUp).Row
    
    With wbDataSource1.Worksheets("Sheet1")
        
'           Last row in the source data
            iLastRowSource = .Range("E1").Cells(Rows.Count, 1).End(xlUp).Row

'           Copy then paste the data
            .Range("A1").Resize(iLastRowSource, 5).Copy wsTargetSheet.Range("B1").Offset(iLastRowTarget)
            
    End With
    
'   ~~~~~ Data Sheet 2 ~~~~~
    
'   Last row in column H in the target worksheet.
    iLastRowTarget = wsTargetSheet.Range("H1").Cells(Rows.Count, 1).End(xlUp).Row
    
    With wbDataSource2.Worksheets("Sheet1")
            
'           Last row in the source data
            iLastRowSource = .Range("E1").Cells(Rows.Count, 1).End(xlUp).Row

'           Copy then paste the data
            .Range("A1").Resize(iLastRowSource, 5).Copy wsTargetSheet.Range("H1").Offset(iLastRowTarget)
            
    End With
    
'   ----------------------------
'       Close Data Sheets
'   ----------------------------
    
    If bCloseAfter _
     Then
        Application.DisplayAlerts = False
            
        wbDataSource1.Close
        
        wbDataSource2.Close
    End If

End Sub