Hi

I have a large spreadsheet with 24 sheets. I need to copy 10 sheets to a new spreadsheet and then save this new sheet.

The problem is that I am doing this at work, and the computers have limited memory. The macro that does this copying works for 5 sheets, but any more give an "out of memory" error.

Is it possible to copy 5, save the new sheet and then copy the remaining 5 sheets form the old workbook to the new one? The copy sheets method seemds only to work witih the same workbook or a new one...

here is the new code up to now, but it falls over after the first five sheets..

Sub CopyGBPAnalysis2()

Dim NewBook As Workbook
Dim Ctr As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Create a new workbook.
Set NewBook = Workbooks.Add

' Copy the GBP worksheets into the new workbook.

Workbooks("COMPAN new test").Sheets(Array("90_NOTICE_GBP", "180_NOTICE_GBP", "1_YEAR_BOND_GBP", "Deferred Interest", "Reference")).Copy _
before:=NewBook.Sheets(1)

MsgBox ("First 5 tabs pasted")

' save as draft

ActiveWorkbook.SaveAs Filename:="\\ANJYFP01\DATA\livedata\Business Analysis\Reporting\Outbound Reporting\Marketing and Sales\Competitor Analysis\GBP\GBP_Comp_Rates_Dft.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False

Workbooks("COMPAN new test").Activate
Workbooks("COMPAN new test").Sheets(Array("TOP_10_GBP", "HICA_GBP", "TRACKER_GBP", "CALL_GBP", "30_NOTICE_GBP")).Copy _
before:=Workbooks("GBP_Comp_Rates_Dft").Sheets(1)

MsgBox ("Next 5 tabs pasted")

Workbooks("Draft").Save
MsgBox ("Draft saved")
' Delete all of the other sheets in the new workbook. The
' initial value of the counter is 1 greater than the number of
' worksheets that you want to copy into the new workbook.
For Ctr = 11 To Workbooks("GBP_Comp_Rates_Dft").Sheets.Count

Workbooks("GBP_Comp_Rates_Dft").Sheets(3).Delete

Next
Workbooks("GBP_Comp_Rates_Dft").Close
MsgBox ("Draft closed")
End Sub


Thanks for any help....


Willow