consolidate data from multiple excelsheets and append the same to main sheet.

Copper Contributor

I have the below requirement.

I need to copy the data from second row for each of the worksheets and consolidate them to main excel sheet by appending one by one .



If main worksheet is not empty then need to calculate the last occupied cell and from there need to append the data from all the worksheets one by one .


Need advise for a VBA script.


attaching a sample excel workbook where on import worksheets need to consolidate the other sheets.

3 Replies



Please use this code:


Sub Test()

    Dim lrow As Long
    Dim sht As Worksheet

    'define last row
    lrow = Sheets("Import").Cells(Rows.Count, 1).End(xlUp).Row
    For Each sht In ThisWorkbook.Sheets
        If sht.Name <> "Import" Then
            'Move the data from cells (Copy and Paste)....
            sht.Range("A2:D2").Copy Sheets("Import").Range("A" & lrow + 1)
            lrow = lrow + 1 'increment last row by one....
        End If

End Sub

@Matt Mickle thanks .


but now its only copying the row2 from all the excel sheets

I need to copy all the rows from all the excel sheet excluding the row1.And each excel sheet the row the row count will vary.

could you please advise.