Subtotal in samll file take a lot of time (VBA)
 

Hi,
I have big xls file. I run macro on this file and in the macro I add new
workbook by:
Set NewBook = Workbooks.Add
In the new workbook I add data (between 5 to 100 rows) and the create
subtotal in the new workbook:
newWB.Activate
newWB.Sheets(1).Activate
ActiveCell.CurrentRegion.Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

It take a lot of time to the macro for run Subtotal (look like it calculate
the big file that take many times).
How can I do the subtotal would not calculate the big file
(Application.Calculation = xlManual)
(When I make manually the subtotal on the new workbook it take a second)

Thanks,
Shlomit

Subtotal in samll file take a lot of time (VBA)
 

Just a guess...

After you add your 5 to 100 rows, stop your macro.

Check to see where the last used cell is (hit ctrl-end).

Maybe you're subtotalling more data than you think -- up to a million rows in
xl2007!

(I don't see anything that looks like it would take a long time in your code.)



Shlomit wrote:

Hi,
I have big xls file. I run macro on this file and in the macro I add new
workbook by:
Set NewBook = Workbooks.Add
In the new workbook I add data (between 5 to 100 rows) and the create
subtotal in the new workbook:
newWB.Activate
newWB.Sheets(1).Activate
ActiveCell.CurrentRegion.Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

It take a lot of time to the macro for run Subtotal (look like it calculate
the big file that take many times).
How can I do the subtotal would not calculate the big file
(Application.Calculation = xlManual)
(When I make manually the subtotal on the new workbook it take a second)

Thanks,
Shlomit

--

Dave Peterson