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
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
Last Comment
Saqib Husain
dwe0608, Please throw in some sample data in an excel file and I shall try to workout something, possibly without VBA
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
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
ASKER
Anybody?
Try this macro on a sheet which does not have the balances and carry forwards inserted.
Saqib
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
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
And can we comment the code so I can learn what we are doing ?
Regards and MTIA
DWE
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
MTIA
DWE
Change line 23 to
Set tprng = Range(pbl).Offset(0, 2)
Set tprng = Range(pbl).Offset(0, 2)
ASKER CERTIFIED SOLUTION
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.
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 ?
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
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
0 means at the same location, either row or column.
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)
hpb.Location.Offset(1, 0)
which one is correct - offset(row,Column) or Offset(column,row)
offset(row,Column)
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