CODEWITHSUNDEEP
vbaexcel
luke
luke

Reputation: 482

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.

Workbook before it copies enter image description here

Workbook after it copies enter image description here

Upvotes: 3

Views: 7562

Answers (2)

TylerH
TylerH

Reputation: 21100

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)

Upvotes: 2

Fadi
Fadi

Reputation: 3322

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

Upvotes: 0

Related Questions