um novo arquivo.

Sub JuntarArquivos()
Dim wkbDestino As String
Dim WorkbookName(1 To 99) As String
Dim ws As Worksheet
Dim i As Integer
i = 1
For Each Workbook In Workbooks
i = 1 + 1
WorkbookName(i) = Workbook.Name
Next Workbook
Total = i
Workbooks.Add
wkbDestino = ActiveWorkbook.Name
For i = 1 To Total
Workbooks(i).Activate
For Each ws In Workbooks(i).Worksheets
ws.Copy
after:=Workbooks(wkbDestino).Sheets(Workbooks(wkbDestino).Sheets.Count)
Next ws
Next i
Application.DisplayAlerts = False
For i = 1 To 3
Sheets(1).Delete
Next i
Application.DisplayAlerts = True
End Sub

35. Deletar abas que estão vazias

Esta macro verifica todas as planilhas para encontrar alguma com todas as
células em branco, uma vez que sejam encontradas, serão deletadas.

Sub DeletarAbasVazias()
Dim Ws As Worksheet