将所有工作簿工作表复制到新的工作簿VBA [英] copy all workbook sheets to a new workbook VBA

问题描述

我正在使用此代码将工作簿中的每张工作表复制到一个新的工作簿中，并且可以正常工作，但是会颠倒工作表的顺序，是否有任何办法可以阻止它执行此操作?

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.

工作簿在复制之前

复制后的工作簿

推荐答案

摘自Scott Craner的评论，OP回复说它奏效了:

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

更改

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

收件人:

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

这篇关于将所有工作簿工作表复制到新的工作簿VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！