sum every 10 rows for all sheets

[Roadhouse]

Hi can someone help me with a vba macro for all the sheets in the workbook I need to sum column E every 10 rows for example

sum(E1:E10)
sum(E11:E20)
sum(E21:E30)

Write the answers in column G and H

so column G and column H
E1:E10, 14

There are 900,000 rows on each sheet

[YasserKhalil]

Hello
Try this code

Please Login or Register  to view this content.

[Roadhouse]

Hi YasserKhalil thanks for the reply I got a run time error when I ran the code this part was highlighted For i = LBound(a) To UBound(a) Step 10, as soon as I press debug and reset. Then I check the sheets I see the code has worked but I still get the error each time I run

[YasserKhalil]

Can you provide sample of your workbook ..

[Trebor76]

Gee, at 900,000 rows per sheet you're really pushing Excel to the limit. Seems more like an Access Db solution to me.

That said try this:

Please Login or Register  to view this content.

Will take some time run however.

Robert

[Roadhouse]

Hi YasserKhalil I put the macro in another workbook and ran it and there was no problems I think it was just a corrupt workbook, thanks for your help

[Roadhouse]

Hi Trebor76 thanks for the reply your macro worked but only on the first sheet I need it to work on all the sheets.

[YasserKhalil]

You're welcome. Glad it worked for you

[mjr veverka]

and another example, clone with Do ... Loop - for one sheet:

Please Login or Register  to view this content.

and for many sheets:

Please Login or Register  to view this content.

[Trebor76]

Hi Trebor76 thanks for the reply your macro worked but only on the first sheet I need it to work on all the sheets.

It did run on every sheet but because I didn't initialise the lngOutputRow variable it put the formula after the last row from Sheet1, then Sheet2...

Try this slightly modified code:

Please Login or Register  to view this content.

Regards,

Robert

[Roadhouse]

Hi Trebor76 that change worked the macro runs on all sheets thanks for your help

[Roadhouse]

Hi porucha vevrku, thanks for the reply your macro worked

[mjr veverka]

All macros work, you can put them on the auction ... ... we take 10% only ...