Merge data from different tabs into one in Excel

Question

I have 18 spreadsheets (tabs) with user data and I need to copy and paste the data into another spreadsheet (tab) of in the consolidated name. I need to copy the header only from the first sheet and at the end remove the empty lines.

It’s all in one workbook. The closest scenario I got was this but it’s too slow:

Sub juntarfim()
    '
    Range("A1").Select
    Sheets("PLANILHA1").Select
    Range("A5:E500").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CONSOLIDADOJUNTOS").Select
    ActiveSheet.Paste
    '
    Range("A501").Select
    Sheets("PLANILHA2").Select
    Range("A6:E500").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CONSOLIDADOJUNTOS").Select
    ActiveSheet.Paste
    '
End sub

Answer 1

if you have Excel 2010+, you can use Power Query instead of VBA macros. It’s easier and more efficient. Follow these steps:

Here I do a little review: What are the differences between Power Query, Power Pivot, Power BI?

In the case of the macro, you can revise line by line with F8:

Sub simpleXlsMerger()
Dim i, WS_Count, WS_Count2 As Integer
Application.ScreenUpdating = False

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "All"
ActiveWorkbook.Worksheets(1).Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("All").Select
Range("A1").Select
ActiveSheet.Paste

WS_Count = Workbooks("test.xlsx").Worksheets.Count 'contar quantas folhas estão no livro

For i = 1 To WS_Count - 1 'Nesta linha, faça uma iteração para cada página do livro, exceto ALL
    ActiveWorkbook.Worksheets(i).Activate
    Range("A2:C" & Range("A65536").End(xlUp).Row).Copy
    ActiveWorkbook.Sheets("All").Activate
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
Next i
End Sub