Copying sheets into new workbook

[IRish3538]

Hey guys I'm trying to spped up some code and just wondered if there's a more efficient way of copying sheets and saving as a new workbook. Maybe there's some way of saving it without opening a new instance of excel (which I assume is what's causing the lag)..

this comes from another sub that has screenupdating turned off btw.

[VBA]
Sub Export_Scope()

fldr = ActiveWorkbook.Path

Sheets(Array("Scope")).Select
Sheets(Array("Scope")).Copy

ActiveWorkbook.SaveAs (fldr & "\Scope")
Workbooks("Scope.xlsx").Close

Sheet1.Select

End Sub[/VBA]

[IRish3538]

also, i know it's not an array.. Older version of my workbook used to copy over more than one sheet... I guess I was just lazy that day when I updated it.

[Teeroy]

Your orignal code creates a copy of the workbook, not the sheet, since the sheet data you copied isn't pasted anywhere. Try the following it copies only the used data (less memory intensive) of the sheet to a new 1 sheet workbook.

I've changed Activeworkbook to Thisworkbook (the one containing the code) as I've been caught too many times working on the wrong workbook when running code from the VBE.

[vba]Sub Export_Scope2()
Dim newWB As Workbook

fldr = ThisWorkbook.Path
Set newWB = Workbooks.Add(1)
ThisWorkbook.Sheets("Scope").UsedRange.Copy Destination:=newWB.Sheets(1).Range("A1")
newWB.Sheets(1).Name = "Scope"
newWB.SaveAs (fldr & "\Scope")
Workbooks(newWB.Name).Close

End Sub[/vba]

[IRish3538]

Thanks for your help..

I'm not sure what you mean about my code copying the workbook, it only copies the sheet(s) in the array into a new workbook. So they're essentially the same. (Clocked around 1.7 sec)

The only difference is that since the code that I have copies the whole sheet, some of the sheet properties arer retained (i.e.hidden rows/columns, zoom). Copying to used range would be great if it was solely a datadump.

I was trying to find some sneaky workaround for not actually openning a new workbook since that increases the run time. The equivalent of copying a workbook using "saveas".

Not sure if I'm making sense or not, but after looking all over, I don't think that it's possible.

[Teeroy]

Doh! When I first tested your code (excel 2010) it gave me a complete copy of the workbook and made me assume it didn't work correctly. After your last post I tried in excel 2003 and it worked. I now can't replicate the error in 2010... @#$% I hate that .

Testing both methods yours clocks about 10% faster (.275 vs .3 over 3 runs). It doesn't appear to open a new Application instance though (per your first post). If you're doing this a number of times, which I guess you are if you're looking for a speed saving, try going to manual calculation and turning alerts off as well as screenupdating. Easiest to do this via Kenneth Hobbs subroutines in http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035.