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.