Splitting Excel workbook sheets 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:\ &amp; &lt;name of manager from group&gt; &amp; ".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.
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(...  &amp; "/" &amp; str_manager &amp; "/" &amp; str_employee &amp; ".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