I have a macro set up that imports a query from access and adds page breaks
where I need them. (at the end of each group) I do not want them on seperate
spreadsheets. Is there a way to set up a macro that when I run it, it will
insert a blank line at the end of each group and then put a formula to add.
My problem is: the next time I import I may have more rows of information in
one group and maybe less in another. The macro I created will add a blank
row and formula to the same place each time (and this maybe in the middle of
that group). Is it possible to tell excel to find the page break and add a
blank row above it and then put a sum formula in the correct column (the
column is D and E.) (I wanted the formula in column D and E at the end of
each page).

Pam

not sure if this will help. The following routine loops through the data
and, when the value in column A changes, inserts a blank row. It then loops
through again establishing the start and end of each block of data and, when
it finds a blank row, inserts a SUM into columns D and E.

Dim LastRow As Long
Dim StartRow As Long
Dim EndRow As Long

Application.ScreenUpdating = False
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < _
Range("A" & i - 1).Value Then
Range("A" & i).EntireRow.Insert
End If
Next
LastRow = Range("A65536").End(xlUp).Row
For i = 2 To LastRow + 1
If Range("A" & i).Value < "" Then
If StartRow = 0 Then
StartRow = i
End If
Else
EndRow = i - 1
Range("D" & i).Formula = _
"=SUM(D" & StartRow & ":D" & EndRow & ")"
Range("E" & i).Formula = _
"=SUM(E" & StartRow & ":E" & EndRow & ")"
StartRow = 0
End If
Next
Application.ScreenUpdating = True


It doesn't take any account of Page Breaks. You might be able to modify it
to look for Page Breaks instead but I haven't had time to try that. Not
sure if you need to do anything if the Page Break is at the start of a block
anyway. If you insert a lot of additional blank lines you might need to
adjust your Page Breaks.

Regards

Trevor


"Pam Coleman" wrote in message
...
I have a macro set up that imports a query from access and adds page
breaks
where I need them. (at the end of each group) I do not want them on
seperate
spreadsheets. Is there a way to set up a macro that when I run it, it
will
insert a blank line at the end of each group and then put a formula to
add.
My problem is: the next time I import I may have more rows of information
in
one group and maybe less in another. The macro I created will add a
blank
row and formula to the same place each time (and this maybe in the middle
of
that group). Is it possible to tell excel to find the page break and add
a
blank row above it and then put a sum formula in the correct column (the
column is D and E.) (I wanted the formula in column D and E at the end of
each page).