将所有工作簿工作表复制到新工作簿 VBA

我正在使用此代码将工作簿中的每张工作表复制到新工作簿中，它工作正常，但它颠倒了工作表的顺序，是否有办法阻止它这样做？

Sub copy()

'copies all the sheets of the open workbook to a new one
Dim thisWb As Workbook, wbTemp As Workbook
Dim ws As Worksheet

On Error GoTo Whoa

Application.DisplayAlerts = False

Set thisWb = ThisWorkbook
Set wbTemp = Workbooks.Add

On Error Resume Next
For Each ws In wbTemp.Worksheets
    ws.Delete
Next
On Error GoTo 0

For Each ws In thisWb.Sheets
    ws.copy After:=wbTemp.Sheets(1)
Next
wbTemp.Sheets(1).Delete

'save vba code here
Application.Dialogs(xlDialogSaveAs).Show Range("CA1").Text & "- (Submittal) " & Format(Date, "mm-dd-yy") & "_" & Format(Time, "hhmm") & ".xlsx"



LetsContinue:
Application.DisplayAlerts = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue

End Sub

我正在复制所有工作表，以便可以将其另存为不同的文件扩展名，这是我发现有效的唯一方法。

Workbook before it copies

Workbook after it copies

来自 Scott Craner 的评论，OP 回复表明它有效：

Change

ws.copy After:=wbTemp.Sheets(1)

to:

ws.copy After:=wbTemp.Sheets(wbTemp.Worksheets.Count)