Spreadsheet count and Totals printing on separate pages.

[cassidybklyn]

Hello everyone,
I need your assistance: I am runing VB 6.0.
I created a spreadsheet from access table. Everything is working fine, except that I am generating both Total and count, but when I print the spreadsheet, the count prints on one page and the total prints on the next page. How can I get both the count and Total to print or lined up on same page?

Here is my code:
================
With xlApp
.Selection.Subtotal GroupBy:=7, TotalList:=Array(7), PageBreaks:=True, _
Function:=xlCount, SummaryBelowData:=True
.Selection.Subtotal TotalList:=Array(4), _
Function:=xlSum, SummaryBelowData:=True, Replace:=False
End With

Here is my page Setup for the spreadsheet:
==========================================

With ActiveSheet.PageSetup
.Order = xlOverThenDown
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.CenterHorizontally = True
.PrintTitleRows = ActiveSheet.Rows("1:1").Address
.PrintGridlines = True
.CenterHeader = "&""Arial,Bold""*** UNCLAIMED CHECKS OLDER THAN 60 DAYS ***"
.CenterFooter = "Page &P of &N"
End With

Thanks.
Cassidy.

 

[SkipVought]

Hi,

"...count prints on one page and the total prints on the next page. "

You are referring to the Data>Subtotals... feature of Excel. When you PRINT, the Totals appear on the subsequent page.

Either decrease your top/bottom margins or put the TOTALS at the TOP of every page, using PageSetup - Rows to repeat at top. You can get this aggregation in ROW 1, for instance, by using the MAX function.

Skip,

_[red][/red]

 

[cassidybklyn]

Hi Skip,
Yes, you are correct; the total appears on one page and the count appears on the next page for the same item, because I am doing grouping.
However, I am not using the Data>Subtotal feature of Excel. I am doing all processing thru VB code:

Here is my code:

With xlApp
.Selection.Subtotal GroupBy:=7, TotalList:=Array(7), PageBreaks:=True, _
Function:=xlCount, SummaryBelowData:=True
.Selection.Subtotal TotalList:=Array(4), _
Function:=xlSum, SummaryBelowData:=True, Replace:=False
End With

Here also is my page setup code:
With ActiveSheet.PageSetup
.Order = xlOverThenDown
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.CenterHorizontally = True
.PrintTitleRows = ActiveSheet.Rows("1:1").Address
.PrintGridlines = True
.CenterHeader = "&""Arial,Bold""*** UNCLAIMED CHECKS OLDER THAN 60 DAYS ***"
.CenterFooter = "Page &P of &N"
End With

Is there any way I can attach the actual spreadsheet so you can see exactly what I mean?

Thanks.

 

[cassidybklyn]

Skip,
I sorta figured out something I was doing wrong. When I did the count, I grouped by a field (L3) and when I did the sum, I also grouped by the Check amount field and I was also page breaking on both fields. So I subsequently removed the second group by clause/pagebreak and now both Count and sum are on the same page.

I guess its all about tweaking.
Thanks.
Cassidy.

 

[SkipVought]

This was really a Forum68 question.

Skip,

_[red][/red]