Avatar of dwe0608
dwe0608
Flag for Australia asked on

Formatting a worksheet for printing

Hi Guys

I have a formatting question .... I have a sheet with approximtely 3000 rows of data. This prints nicely on A4 paper. I can use the header and foot functionality to print the spreadsheet with headers and footer. But I need to print the headers and footers like a bank statement, with carried forward balances on the top and bottom of each page

The way I see it working is to have a row inserted at the top of each page, with the requisite information in it. For example, I see a worksheet in front of me with Page Break View on - the program knows where the top and bottom of each page is - I need to programmatically insert a row, and fill in the carried forward and balance details at the top and bottom of each page (except the first which would have only a balance at the bottom.

Does that make sense? I would like to use VBA on this.

An example of what I would like the doucment to look like is attached. The green coloured cells are what I would imagine would be inserted by the VBA code

MTIA

Darrin example.xls
Microsoft Excel

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
Rob Henson

I know that is possible in Access but it would have to be  VBA driven in Excel.

The only other way that I can think of would be to set a fixed number of rows per page and hardcode the page breaks rather than automatically set them. Then have a running total column with conditional formatting based on the row number.

Cheers
Rob H
Saqib Husain

dwe0608, Please throw in some sample data in an excel file and I shall try to workout something, possibly without VBA
dwe0608

ASKER
Hi guys

there is a sample file with the question

I did think about using a fixed number of rows as a page, then insert the row, but this wont work as the rows have varying heights.

In page break-previewthe page is marked out - the program is able to discern the length and width of the page - hopefully this will be available through the VBA model. Is there a way to see what page a row is on ? If that is possible, I would imagine looping through the rows to get the last row on the first page and insert the row with the balance, then loop and get the first row on the second page and insert the row with the carried forward balance.

MTIA

Darrin
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dwe0608

ASKER
Anybody?
Saqib Husain

Try this macro on a sheet which does not have the balances and carry forwards inserted.

Saqib
Sub pageSubtotals()
Dim aws As Worksheet
Set aws = ActiveSheet
ActiveWindow.View = xlPageBreakPreview
i = 1
Set tprng = Range("C4")
Do
    pbl = ActiveSheet.HPageBreaks.Item(i).Location.Address
    Range(pbl).Select
    Set hpb = ActiveSheet.HPageBreaks.Item(i)
    Range(hpb.Location.Offset(-2, 0), hpb.Location.Offset(1, 0)).EntireRow.Insert
    Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
    Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
    Range(pbl).Offset(0, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
    Range(pbl).Offset(1, 0).EntireRow.Interior.Color = Range(pbl).Offset(-3, 0).Interior.Color
    Range(pbl).EntireRow.PageBreak = xlPageBreakManual
    Range(pbl).Offset(-1, 2).NumberFormat = Range(pbl).Offset(-3, 2).NumberFormat
    Range(pbl).Offset(-1, 2).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
    Range(pbl).Offset(0, 2).NumberFormat = Range(pbl).NumberFormat
    Range(pbl).Offset(0, 2) = "=" & Range(pbl).Offset(-1, 2).Address
    Range(pbl).Offset(-1, 3) = "Balance"
    Range(pbl).Offset(0, 3) = "Carried forward"
    Set tprng = Range(pbl)
    i = i + 1
Loop While i <= ActiveSheet.HPageBreaks.Count
pbl = Range("C" & ActiveSheet.Rows.Count).End(xlUp).Offset(3, 0).Address
Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(-1, 0).NumberFormat = Range(pbl).Offset(-3, 0).NumberFormat
Range(pbl).Offset(-1, 0).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
Range(pbl).Offset(-1, 3) = "Total"
ActiveWindow.View = xlNormalView
End Sub

Open in new window

dwe0608

ASKER
hi ssaqibh - the idea is good and for all intents and purposes it works, what it doesnt not do however is calculate the subtotals correctly - for example the sub-total carried forward on the first page is premised on the cells =SUM($A$44:$C$83) when it should be =SUM($C$44:$C$83) ... can we fix that ?

And can we comment the code so I can learn what we are doing ?

Regards and MTIA

DWE
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dwe0608

ASKER
Another things we should work on while the idea is fesh in our minds - is how to undo the insertion - maybe on the column to the rightmost, at each balance / carried forward insertion we insert a code - say ## and if we want to remove our formatting we find that on the row, and delete those rows? Does that make sense ?

MTIA

DWE
Saqib Husain

Change line 23 to

    Set tprng = Range(pbl).Offset(0, 2)
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dwe0608

ASKER
where hpb.Location.Offset(1, 0) appears in the code, offset is referring to the horizontal pagebreaks, I get that, but what is 1,0 referencing ? row1, column 0 or ... ? - where does row 0 lay - immediately above or below the pagebreak ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dwe0608

ASKER
great answer ... but I need to understand a little more through the coding being commented :-)
Saqib Husain

I have commented a few of the out-of-the-routine statements. I presume that with your learning you can see what the others do.

If you still have any questions then do ask. If for some reason I do not respond here feel free to email me to get my attention. I shall then respond here.

Thanks for the points and the grading

Saqib
Sub pageSubtotals()
Dim aws As Worksheet
Set aws = ActiveSheet
ActiveWindow.View = xlPageBreakPreview
i = 1

'This is a variable used for determining the starting of the sum range
Set tprng = Range("C4")

'For each of the pagebreaks. The "for each" method does not work here if the number
' of page breaks increases while inserting rows.
Do
    pbl = ActiveSheet.HPageBreaks.Item(i).Location.Address
    Range(pbl).Select
    Set hpb = ActiveSheet.HPageBreaks.Item(i)

'this is the range starting from two rows above the horizontal pagebreak
'down to one row below the pagebreak (a total of 4 rows, one each for the 
'balance and carry over and one each above and below them)
    Range(hpb.Location.Offset(-2, 0), hpb.Location.Offset(1, 0)).EntireRow.Insert

'I bet you know what this is
    Range(Range(pbl).Offset(-2, 0), Range(pbl).Offset(1, 0)).Offset(0, 4) = "##"

    Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
    Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
    Range(pbl).Offset(0, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
    Range(pbl).Offset(1, 0).EntireRow.Interior.Color = Range(pbl).Offset(-3, 0).Interior.Color
    Range(pbl).EntireRow.PageBreak = xlPageBreakManual
    Range(pbl).Offset(-1, 2).NumberFormat = Range(pbl).Offset(-3, 2).NumberFormat
    Range(pbl).Offset(-1, 2).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
    Range(pbl).Offset(0, 2).NumberFormat = Range(pbl).NumberFormat
    Range(pbl).Offset(0, 2) = "=" & Range(pbl).Offset(-1, 2).Address
    Range(pbl).Offset(-1, 3) = "Balance"
    Range(pbl).Offset(0, 3) = "Carried forward"
    Set tprng = Range(pbl).Offset(0, 2)
    i = i + 1
Loop While i <= ActiveSheet.HPageBreaks.Count
pbl = Range("C" & ActiveSheet.Rows.Count).End(xlUp).Offset(3, 0).Address
Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(-1, 0).NumberFormat = Range(pbl).Offset(-3, 0).NumberFormat
Range(pbl).Offset(-1, 0).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
Range(pbl).Offset(-1, -1) = "Total"
Range(Range(pbl).Offset(-2, 0), Range(pbl).Offset(-1, 0)).Offset(0, 2) = "##"
ActiveWindow.View = xlNormalView
End Sub

Open in new window

Saqib Husain

0 means at the same location, either row or column.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dwe0608

ASKER
thanks for the commenting and I am following it except for

hpb.Location.Offset(1, 0)

which one is correct - offset(row,Column) or Offset(column,row)
Saqib Husain

offset(row,Column)