Export Multiple Sheets and Save As a new file

[vmjamshad]

Hi All,
I am trying to export some sheets from my workbook and save it in another location. Please see the code below:

Sub ExportSheets2()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets(Array("Xport1", "Xport2")).Copy
ActiveWorkbook.SaveAs "C:\Documents\Test123.xlsx"
End Sub

and here instead of File Names ("Xport1", "Xport2"),

ThisWorkbook.Sheets(Array("Xport1", "Xport2")).Copy

I want to use sheet name variables

Dim xp1, xp2 As Worksheet
Set xp1 = Sheet1
Set xt2 = Sheet2
Set wb = Workbooks.Add
ThisWorkbook.Sheets(xp1, xp2).Copy

Can you help me with the code
Thanks in advance

[p45cal]

Dim xp1 As Worksheet, xp2 As Worksheet
Set xp1 = Sheet1
Set xp2 = Sheet2
Sheets(Array(xp1.Name, xp2.Name)).Copy

or forget the setting of variables and use

Sheets(Array(Sheet1.Name, Sheet2.Name)).Copy

By the way, you don't need workbooks.add; .copy without anything after it creates a new workbook.

[vmjamshad]

Thanks, This helped me to learn