Formatting Totals at Bottom of Page

I have some csv data the I pull into Excel to format and etc. The data rows
are always different (from month to month). One time I will have 400 rows,
the next time I might have 58. Is there a way to force Excel to Autosum
defined columns on each page without having to Insert a row at the bottom of
each page?

Intuitively, everyone wants to the see the totals row at the bottom of the
sheet. As you have discovered, the simplest thing to do is to put the totals
row at the top of the sheet! The first row would contain sums, the second
row might contain labels and the third row on down might contain data. Doing
this would allow the sum to be, for example:

=SUM(A3:A65536)

You just don't have to care how many "real" rows of data there are.
--
Gary''s Student


"Scott" wrote:

> I have some csv data the I pull into Excel to format and etc. The data rows
> are always different (from month to month). One time I will have 400 rows,
> the next time I might have 58. Is there a way to force Excel to Autosum
> defined columns on each page without having to Insert a row at the bottom of
> each page?

You could find the next available row (I used column A):

dim NextRow as long
with activesheet
nextrow = .cells(.rows.count,"A").end(xlup).row + 1

.cells(nextrow,"B").resize(1,5).formulaR1C1 _
= "=sum(r1c:r[-1]c)"
end with

Another option would be to insert a new row 1 and use that to hold your
formulas.

Then you wouldn't have to care where the data stops.

You could use a formula like:
=sum(B2:B65536)



Scott wrote:
>
> I have some csv data the I pull into Excel to format and etc. The data rows
> are always different (from month to month). One time I will have 400 rows,
> the next time I might have 58. Is there a way to force Excel to Autosum
> defined columns on each page without having to Insert a row at the bottom of
> each page?

--

Dave Peterson

Thanks for the prompt response! If I understand you correctly, this cannot be
done at the bottom of the respective page, but CAN be accomplished at each
subsequent page using the "Rows to Repeat at Top" value. Is this correct?

Thanks again!

"Gary''s Student" wrote:

> Intuitively, everyone wants to the see the totals row at the bottom of the
> sheet. As you have discovered, the simplest thing to do is to put the totals
> row at the top of the sheet! The first row would contain sums, the second
> row might contain labels and the third row on down might contain data. Doing
> this would allow the sum to be, for example:
>
> =SUM(A3:A65536)
>
> You just don't have to care how many "real" rows of data there are.
> --
> Gary''s Student
>
>
> "Scott" wrote:
>
> > I have some csv data the I pull into Excel to format and etc. The data rows
> > are always different (from month to month). One time I will have 400 rows,
> > the next time I might have 58. Is there a way to force Excel to Autosum
> > defined columns on each page without having to Insert a row at the bottom of
> > each page?

Dave,

I guess what I'm not understanding is how to display the Totals for each
page. Ultimately, what I would like to do is display the autosum at the
bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the
page totals. This continuing on for the continual pages =SUM(A56:A107) and
A:108 being the next page totals.

Maybe the VB code you referenced will handle this, I'm just not well versed
with VB code and applying it to an existing spreadsheet.

Scott

"Dave Peterson" wrote:

> You could find the next available row (I used column A):
>
> dim NextRow as long
> with activesheet
> nextrow = .cells(.rows.count,"A").end(xlup).row + 1
>
> .cells(nextrow,"B").resize(1,5).formulaR1C1 _
> = "=sum(r1c:r[-1]c)"
> end with
>
> Another option would be to insert a new row 1 and use that to hold your
> formulas.
>
> Then you wouldn't have to care where the data stops.
>
> You could use a formula like:
> =sum(B2:B65536)
>
>
>
> Scott wrote:
> >
> > I have some csv data the I pull into Excel to format and etc. The data rows
> > are always different (from month to month). One time I will have 400 rows,
> > the next time I might have 58. Is there a way to force Excel to Autosum
> > defined columns on each page without having to Insert a row at the bottom of
> > each page?
>
> --
>
> Dave Peterson
>

That's not what I meant. You just got suggestions to have a grand total line at
the bottom of all your data.

There's nothing built into excel that does what you want (subtotals per page).



Scott wrote:
>
> Dave,
>
> I guess what I'm not understanding is how to display the Totals for each
> page. Ultimately, what I would like to do is display the autosum at the
> bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the
> page totals. This continuing on for the continual pages =SUM(A56:A107) and
> A:108 being the next page totals.
>
> Maybe the VB code you referenced will handle this, I'm just not well versed
> with VB code and applying it to an existing spreadsheet.
>
> Scott
>
> "Dave Peterson" wrote:
>
> > You could find the next available row (I used column A):
> >
> > dim NextRow as long
> > with activesheet
> > nextrow = .cells(.rows.count,"A").end(xlup).row + 1
> >
> > .cells(nextrow,"B").resize(1,5).formulaR1C1 _
> > = "=sum(r1c:r[-1]c)"
> > end with
> >
> > Another option would be to insert a new row 1 and use that to hold your
> > formulas.
> >
> > Then you wouldn't have to care where the data stops.
> >
> > You could use a formula like:
> > =sum(B2:B65536)
> >
> >
> >
> > Scott wrote:
> > >
> > > I have some csv data the I pull into Excel to format and etc. The data rows
> > > are always different (from month to month). One time I will have 400 rows,
> > > the next time I might have 58. Is there a way to force Excel to Autosum
> > > defined columns on each page without having to Insert a row at the bottom of
> > > each page?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Is there any way to build a template sheet and any csv data importted into
the worksheet is then merged into that template? Just fishing!


"Dave Peterson" wrote:

> That's not what I meant. You just got suggestions to have a grand total line at
> the bottom of all your data.
>
> There's nothing built into excel that does what you want (subtotals per page).
>
>
>
> Scott wrote:
> >
> > Dave,
> >
> > I guess what I'm not understanding is how to display the Totals for each
> > page. Ultimately, what I would like to do is display the autosum at the
> > bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the
> > page totals. This continuing on for the continual pages =SUM(A56:A107) and
> > A:108 being the next page totals.
> >
> > Maybe the VB code you referenced will handle this, I'm just not well versed
> > with VB code and applying it to an existing spreadsheet.
> >
> > Scott
> >
> > "Dave Peterson" wrote:
> >
> > > You could find the next available row (I used column A):
> > >
> > > dim NextRow as long
> > > with activesheet
> > > nextrow = .cells(.rows.count,"A").end(xlup).row + 1
> > >
> > > .cells(nextrow,"B").resize(1,5).formulaR1C1 _
> > > = "=sum(r1c:r[-1]c)"
> > > end with
> > >
> > > Another option would be to insert a new row 1 and use that to hold your
> > > formulas.
> > >
> > > Then you wouldn't have to care where the data stops.
> > >
> > > You could use a formula like:
> > > =sum(B2:B65536)
> > >
> > >
> > >
> > > Scott wrote:
> > > >
> > > > I have some csv data the I pull into Excel to format and etc. The data rows
> > > > are always different (from month to month). One time I will have 400 rows,
> > > > the next time I might have 58. Is there a way to force Excel to Autosum
> > > > defined columns on each page without having to Insert a row at the bottom of
> > > > each page?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

I guess it depends on what you mean by merge.

You could create a macro that would open your .csv file. Then you could have
that same macro create a new workbook based on an existing template.

Then your macro could copy all(?) the data and paste it into a worksheet in that
newly created workbook.

If you're trying to set up a template file that has page breaks already inserted
and formulas that sum per page, you could even copy pieces of the .csv file and
paste into that template worksheet nicely.

But you'll want to error on the conservative side when it comes to inserting
those pagebreaks into your template.

If you create a template that supports 60 rows of details (and a couple of
headers and a couple of "footers"), then change printers, you may find that the
new printer can't fit that many rows per page and things won't look too
nice--and you'll have to rearrange your data or create a nicer template and
change the code that imports the .csv file.



Scott wrote:
>
> Is there any way to build a template sheet and any csv data importted into
> the worksheet is then merged into that template? Just fishing!
>
> "Dave Peterson" wrote:
>
> > That's not what I meant. You just got suggestions to have a grand total line at
> > the bottom of all your data.
> >
> > There's nothing built into excel that does what you want (subtotals per page).
> >
> >
> >
> > Scott wrote:
> > >
> > > Dave,
> > >
> > > I guess what I'm not understanding is how to display the Totals for each
> > > page. Ultimately, what I would like to do is display the autosum at the
> > > bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the
> > > page totals. This continuing on for the continual pages =SUM(A56:A107) and
> > > A:108 being the next page totals.
> > >
> > > Maybe the VB code you referenced will handle this, I'm just not well versed
> > > with VB code and applying it to an existing spreadsheet.
> > >
> > > Scott
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You could find the next available row (I used column A):
> > > >
> > > > dim NextRow as long
> > > > with activesheet
> > > > nextrow = .cells(.rows.count,"A").end(xlup).row + 1
> > > >
> > > > .cells(nextrow,"B").resize(1,5).formulaR1C1 _
> > > > = "=sum(r1c:r[-1]c)"
> > > > end with
> > > >
> > > > Another option would be to insert a new row 1 and use that to hold your
> > > > formulas.
> > > >
> > > > Then you wouldn't have to care where the data stops.
> > > >
> > > > You could use a formula like:
> > > > =sum(B2:B65536)
> > > >
> > > >
> > > >
> > > > Scott wrote:
> > > > >
> > > > > I have some csv data the I pull into Excel to format and etc. The data rows
> > > > > are always different (from month to month). One time I will have 400 rows,
> > > > > the next time I might have 58. Is there a way to force Excel to Autosum
> > > > > defined columns on each page without having to Insert a row at the bottom of
> > > > > each page?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson