merge the lines present in the different sheets of an Excel file (even 400 or more sheets) in the first sheet

[stefanoste78]

Good evening.

I created a python code which, due to a naming error given to the sheet, instead of pasting the rows into the same Excel sheet, one below the others, created as many sheets as there are rows of extracted data by inserting the extracted data into the first line.
Would it be possible to copy and paste the data present in each sheet of the file into the first sheet so as to have the data extracted in a single sheet instead of 400/500 sheets?

Thank you

 

[Monty]

Try This

Sub ConsolidateData()
    Dim ws As Worksheet
    Dim wsDest As Worksheet
    Dim LastRow As Long
    Dim DestLastRow As Long
    
    ' Define the destination sheet
    Set wsDest = ThisWorkbook.Sheets("DestinationSheet") ' Change "DestinationSheet" to the name of your destination sheet
    
    ' Loop through each sheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsDest.Name Then ' Skip the destination sheet itself
            ' Find the last row in the destination sheet
            DestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
            
            ' Find the last row in the current sheet
            LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            ' Copy data from current sheet to destination sheet
            ws.Range("A1:A" & LastRow).Copy Destination:=wsDest.Range("A" & DestLastRow + 1)
        End If
    Next ws
    
    MsgBox "Data consolidation complete!", vbInformation
End Sub

 

[AlanSidman]

An alternative that does not require you do any coding