copy all workbook sheets to a new workbook VBA

Question

I am using this code to copy every sheet in a workbook to a new one and it works fine but it reverses the order of the sheets, would there be anyway to keep it from doing this?

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

I'm copying all the sheets so i can save it as a different file extension and this was the only way i found that worked.

Workbook before it copies

Workbook after it copies

Answer 1

From Scott Craner's comment, which OP replied to indicating it worked:

Change

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

to:

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

Answer 2

If you only want to change the file format

(I'm copying all the sheets so i can save it as a different file extension and this was the only way i found that worked.)

Then you can try this code:

Sub Test()
    fn = Range("CA1").Text & "- (Submittal) " & Format(Now, "mm-dd-yy_hhmm")
    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=fn, fileFilter:="Excel Workbook (*.xlsx), *.xlsx")
    If fileSaveName <> False Then
     Application.DisplayAlerts = False
     ActiveWorkbook.SaveAs fileSaveName, xlOpenXMLWorkbook
     Application.DisplayAlerts = True
    End If
End Sub