PDA

View Full Version : Export Multiple Sheets and Save As a new file



vmjamshad
03-21-2018, 10:16 PM
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
03-22-2018, 09:20 AM
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
04-07-2018, 11:26 PM
Thanks, This helped me to learn