Worksheet copy problem

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

I haven't read the macro extensively, but based on what I think you are doing, I would suggest you consider saving the sheet with a new name, then delete the data you don't want out of the new version of the sheet.

Matt

Thanks for the reply.However, this is does not work because the computer has not enough memeory to hold the 2 spreadsheets while the macros on the original delete the excess sheets on the copy. Also, how would you delete the macros from the new sheet?

Can anyone help with the original query - is it possible to copy whole worksheets to an existing workbook?

Thanks

you might even be able to record this macros:

shift select 5 sheet from the old workbook and right-click on one of them.
then chose "move or copy." then choose the destination workbook, select
"(move to end)" and check off "create copy." - then do it again.

now stop the macro and look at the code to modify to your needs...
very much a noob method but should work for you. good luck..