Splitting Excel workbook sheets

Question

Running Office 2007, I have master workbook with data, which I:

• Split master sheet data to new sheets for every salesmen
• Create new workbook (with 1 sheet)
• Put sheets of salesmen to an array according their manager
• Copy array of sheets to new workbook
• Delete first sheet on newly created workbook, save it as .xlsx and close

My problem is that it takes more time than I expect, around 8 seconds for one group of salesmen (there are 6 at the end) - altogether with formatting before splitting master sheet data around 60 seconds. I think there is problem in my code and with how Excel manages how and how much RAM it uses.

• Master data workbook has 21 MB, .xls format
• When I open it, it uses 50 MB of RAM
• Then when I run code with some formatting etc it grows to 80 MB
• Then I start splitting, where it slowly grows to 87, 95, 103, 110, 125, 132 and then finally to 160 MB, where it copies sheets and close new workbook, then again Excel uses 95 MB of RAM. Merging process creates 6 new workbooks with size from 0.6 to 1.8 MB, where these workbooks contain from 6 to 10 sheets.

Dim wb1 As Workbook, group As Variant, new_wb As Workbook
With Application
 .EnableEvents = False
 .ScreenUpdating = False
 .StatusBar = "Merging .."
End With
Set wb1 = ActiveWorkbook
'
'some formatting on master sheet, splitting to sheets
'adding sheets to an array called "group"
'for every manager do ..
'
Set new_wb = Workbooks.Add(xlWBATWorksheet)
With new_wb
 wb1.Sheets(group).Copy After:=.Sheets(1)
Application.DisplayAlerts = False
 .Sheets(1).Delete
Application.DisplayAlerts = True
.SaveAs C:\ & <name of manager from group> & ".xlsx", FileFormat:=51, .Close
End With
With Application
 .EnableEvents = True
 .ScreenUpdating = True
 .StatusBar = False
End With

However, when I prepared my workbook to the step when copying occurs, and run just one line of code:

wb1.Sheets(Array("Sheet1", "Sheet2","Sheet3","Sheet4")).Copy After:=.Sheets(1)

This way it, copies sheets almost instantly to new a workbook.

I'm not sure what I'm doing wrong. What is the proper way how to copy (Excel/RAM friendly) an array of sheets to a new workbook?

When I run this one line of code, it too uses 160 MB of RAM, only this way it takes enough RAM immediately, not slowly growing more and more.

• Ron Beyer: I wouldn't worry too much about the RAM usage, Excel is a mixed-mode (native and managed) code environment and when resources get allocated and released may not be entirely clear. It is complicated by the fact that it is also machine dependant (how much memory pressure exists). Office has its issues, I've found that I can open a 90 page Word document and just turning off spell check released 500mb of RAM for example.

Answer 1

I had a similar project for a client. I looped a table filter by employee name for the chosen manager. Then I moved the resultant table values to a new sheet and ran activesheet.move to create a new workbook.

I used your 'testname' saveas portion of the code to pass the manager folder variable and employee variable folder within my loop.

Saveas(... & "/" & str_manager & "/" & str_employee & ".xlsx" )

The system stress calculations are interesting but not needed. That is because using arrays, counting, bubble sorting, loops are most efficient depending on the size of the dataset. Mine was quicker when using a table filter loop

Answer 2

The following might be a little more efficient then copying and pasting into a new workbook. This code "Sheets(Array(" will copy what ever array of sheets you wish into a new workbook. That workbook becomes active as soon as it is created, so you grab control of it by newWB=activeworkbook. You can then save the new workbook with any name you wish. Then you go back to your original workbook with myWB.activate before closing the new one. I used this in 2010 successfully and very quickly.

Sub movePagestoNewBook()
Dim myWB As Workbook
Dim newWB As Workbook
Dim defaultFileFormat As Long
 Set myWB = ActiveWorkbook
 Sheets("Sheet5").Activate
 Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
 Set newWB = ActiveWorkbook
 myWB.Activate
 newWB.SaveAs "C:\" & "testName" & ".xlsx", FileFormat:=51
 newWB.Close
End Sub

• mauek unak: Yes, with Office 2010+ versions copying worksheet arrays to new workbook is done instantly, I only have problem with Office 2007, thanks for input though