Code assistance needed

  • Thread starter Thread starter JimMay
  • Start date Start date
J

JimMay

I've just come across this code in my archives, and it isn't
Working. It might be full of errors, but can someone assist is
Getting it on its feet?
Thanks in Advance.
Jim


Sub PrintSubTotalInFooter()
Dim lrow As Long
Dim numhpb As Long
Dim LPage As Long
Dim i As Integer
Dim STRng As Range
Application.EnableEvents = False
' The PageSetUp Print Range has already been set to Range A5:F149
Set STRng = Application.InputBox("Highlight the Range of Amts to be
Subtotaled", Type:=8)
rrows = Range(ActiveSheet.PageSetup.PrintTitleRows).Rows.Count
With ActiveSheet
.PageSetup.RightFooter = ""
numhpb = .HPageBreaks.Count
LPage = numhpb + 1
For i = 1 To numhpb
.PageSetup.RightFooter = "Sub-total = " &
Format(WorksheetFunction.Sum _
(Range(STRng(1).Address, STRng(.HPageBreaks(i).Location.Row
- (1 + rrows)).Address)), "0,000.00")
On Error GoTo ErrorHandler
.PrintPreview i, i
Next i
.PageSetup.RightFooter = "The-total = " &
Format(WorksheetFunction.Sum _
(STRng), "0,000.00")
On Error GoTo ErrorHandler
.PrintPreview LPage, LPage
End With
ErrorHandler:
Application.EnableEvents = True
End Sub
 
Code is working. But before running macro, you need to do few things.

1. Set the print area
2. Set the rows those will be repeated top of the pages.
3. Run the macro
 
Haldun; Thanks for looking at this and offering your comments.
I did as you said, yet when I run Step 3, I get nothing...
<<What I'd expect to be a Print-Preview>>.
 
You need to replace printpreview with printout and use this macro instead of standard print command.

Because of page header and footer are static it is printing the pages one by one.

Code follows.

Sub PrintSubTotalInFooter()
Dim lrow As Long
Dim numhpb As Long
Dim LPage As Long
Dim i As Integer
Dim STRng As Range
Application.EnableEvents = False
' The PageSetUp Print Range has already been set to Range A5:F149
Set STRng = Application.InputBox( _
"Highlight the Range of Amts to be Subtotaled ", Type:=8)
rrows = Range(ActiveSheet.PageSetup.PrintTitleRows).Rows.Count
With ActiveSheet
.PageSetup.RightFooter = ""
numhpb = .HPageBreaks.Count
LPage = numhpb + 1
For i = 1 To numhpb
.PageSetup.RightFooter = "Sub-total = " & Format( _
WorksheetFunction.Sum(Range(STRng(1).Address, _
STRng(.HPageBreaks(i).Location.Row - (1 + rrows)).Address)), _
"0,000.00")
On Error GoTo ErrorHandler
.PrintOut i, i
Next i
.PageSetup.RightFooter = "The-total = " & Format(WorksheetFunction.Sum( _
STRng), "0,000.00")
On Error GoTo ErrorHandler
.PrintOut LPage, LPage
End With
ErrorHandler:
Application.EnableEvents = True
End Sub
 
So there's no way to Preview this before actually Printing it?
Appreciate you help;
Jim
 
might be on the wrong track but will you get a print preview with enable
events=false?
--
paul
(e-mail address removed)
remove nospam for email addy!



JimMay said:
So there's no way to Preview this before actually Printing it?
Appreciate you help;
Jim
Click to expand...
 
Back
Top