Forum Discussion

tridi94's avatar
tridi94
Copper Contributor
Aug 18, 2022

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

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 .

 

Note.

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.

Macros and VBA

3 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Aug 18, 2022

    tridi94 

     

    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
    Next

End Sub
    • tridi94's avatar
      tridi94
      Copper Contributor
      Aug 29, 2022

      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.

       

Resources