Copy multiple sheets to another workbook-CodePudding

I have some code that will copy a sheet and then create a new workbook and then paste it and adjust the sheet name and workbook name before saving it to the Desktop.

All the called subs do is transpose ranges of data from the entry sheet to the export sheet which is the one that is copied (named "Rows") to be pasted into the new workbook.

Code is used as a button and so far no declaration has been required.

Sub UploadSheet()

    Call TransposeHS
    Call TransposeOrigin
    Call TransposeValues
    
    Application.ScreenUpdating = False

    Path = CreateObject("WScript.Shell").specialfolders("Desktop")
    Worksheets("Rows").Cells.Copy

    Workbooks.Add (xlWBATWorksheet)
    ActiveWorkbook.ActiveSheet.Paste
    ActiveWorkbook.ActiveSheet.Name = "Rows"
    ActiveWorkbook.SaveAs Filename:=Path & "\" & "Upload" & ".xlsx"
    ActiveWorkbook.Close SaveChanges:=True

    Application.ScreenUpdating = True

    MsgBox "Exported to Desktop"

End Sub

How do I adjust the code to copy over 2 sheets instead of just the one into a new workbook?

CodePudding user response：

Creating a new workbook with Workbooks.Add and then pasting is overcomplicated.

Call Copy on the worksheet(s) without specifying any parameters; a new workbook is created with the copied sheet(s), and is the ActiveWorkbook.

Worksheets(Array("Rows", "SecondSheet")).Copy
ActiveWorkbook.SaveAs Filename:=Path & "\" & "Upload" & ".xlsx"