Merge data from different tabs into one in Excel

Question

Merge data from different tabs into one in Excel

Navigation

#1 by (1 votes)

2

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

It's all in one workbook only. The closest scenario I got was this but it's very 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

excel excel-vba

asked by anonymous 14.06.2018 / 20:15

1 answer

Pass two variables in the url in javascript Pass value to another page in angular

score 1 · 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:

HereIdoalittlereview: What are the differences between Power Query, Power Pivot, Power BI?

For macro, you can review row 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