Column subtotals in the bottom row of each page

M

MO

I'm working with an unfriendly project estimate worksheet
that requires a project specific Bill of Materials to be
individuaaly listed and priced each time. The BOMs are
from pivot tables with additional colmns added for
pricing. The tasks becomes a slow cut and paste or
a "copy, special paste value and then cell= reference
cell drag" process. Once this hurtle is jump I'm
required to subtotal each page with a recap of each
subtotal on a summary page....whew..

Is there a way to automate subtotals to pages (row:
45,90,135...)once the 2000 line items are in the
worksheet?
Thanks for your consideration.
 
D

Dave Peterson

This sounds exceedingly unfriendly and it doesn't utilize the stuff built into
excel.

But I think if I had to do this, I use Data|subtotals.

But I'd insert a helper column that contained 45 of one value and then 45 of
another. Then use that as the key in Data|subtotals.

Make sure your print range excludes that helper column and it might work.

When you do data|subtotals, make sure you check that "Page break between groups"
box.

When the data changes, just remove the subtotals (also on that data|subtotal
dialog), clear out that helper column, add/change/delete your data and then put
those values back in the helper column and one more Data|subtotals.

If you like this idea, here's a macro that uses column A as the helper column.
It uses column B to determine the lastrow and uses row 1 as a header.

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim iRow As Long
Dim myValue As Boolean
Dim myResize As Long

myStep = 45
With ActiveSheet
FirstRow = 2 'some headers
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
myValue = True
For iRow = FirstRow To LastRow Step myStep
If iRow + myStep > LastRow Then
myResize = LastRow - iRow + 1
Else
myResize = myStep
End If
.Cells(iRow, "A").Resize(myResize).Value = myValue
myValue = Not myValue
Next iRow
End With

End Sub

If you wanted, you could record a macro that removes the subtotals, clears that
helper column and then reapplies data|subtotal.

And maybe it would even work to make your life a little easier.
 
Top