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.

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