Consolidate data from multiple tabs into one (same workbook)

Asked

Viewed 2,777 times

1

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
excel-vba

  • Avoid using the Select, because it is slow and can cause errors in very large codes. Regarding the code, if you can create a [mcve] with some sample data...

1 answer

1


Sub juntarfim()

Application.ScreenUpdating = False 'desativa atualização de tela.
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
  • 1

    thank you very much!!!

Browser other questions tagged excel-vba

You are not signed in. Login or sign up in order to post.