Link to home
Create AccountLog in
Avatar of dwe0608
dwe0608Flag 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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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
dwe0608, Please throw in some sample data in an excel file and I shall try to workout something, possibly without VBA
Avatar of 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
Avatar of dwe0608

ASKER

Anybody?
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

Avatar of 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
Avatar of 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
Change line 23 to

    Set tprng = Range(pbl).Offset(0, 2)
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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 ?
Avatar of dwe0608

ASKER

great answer ... but I need to understand a little more through the coding being commented :-)
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

0 means at the same location, either row or column.
Avatar of 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)
offset(row,Column)