Isto irá listá-los
Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wsList As Worksheet
Dim numSheets As Integer
numSheets = wbEstimate.Sheets.Count
Worksheets.Add(After:=Worksheets(numSheets)).Name = "list"
Set wsList = Worksheets("list")
Dim i As Integer
i = 1
For Each Sheet In wbEstimate.Worksheets
wsList.Cells(i, 1) = Sheet.Name
i = i + 1
Next
End Sub
E isso deve ser copiado se ambos estiverem abertos
Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wbTest As Workbook
Set wbTest = Workbooks("test.xlsx")
For Each Sheet In wbEstimate.Sheets
Sheet.Copy after:=wbTest.Sheets(wbTest.Sheets.Count)
Next
End Sub
E se você precisar abrir o teste antes de copiar as folhas, faça assim:
Sub list()
Dim wbEstimate As Workbook
Set wbEstimate = ThisWorkbook
Dim wbTest As Workbook
Set wbTest = Workbooks.Open("C:\Users\path\to\test.xlsx")
For Each Sheet In wbEstimate.Sheets
Sheet.Copy after:=wbTest.Sheets(wbTest.Sheets.Count)
Next
End Sub