DONT WANT TO "Rows to Repeat at top" on a condtion

[NimishK]

Hi Anyone

This could be quite challenging.

Have almost browsed through all the possible ideas of coding which i could not code of what i want but really wish to have great logic from you.

Kindly guide me so as to where to write the code in thisWorkbook or some other place

Basically I DONT WANT TO "Rows to Repeat at top" on a condtion.

Have attached the file for your reference.
https://www.dropbox.com/s/463fj7kv9a7uexo/Sample555-Sutures.xlsx?dl=0

here mydata Range refers to the items calculated from Row 15 onwards till the row which = "TOTAL" with additional Two rows and within column borders around

Kindly Print Preview
A. If you look at the file i have not coded but selected "Rows to Repeat at Top" = $14:$14 : Also scaled View to 85%

1. it comes correctly on Every Page
2. Now i would like to repeat the above condition with Coding
Condtion 1 : if mydata range i.e from row 15 to row 71 exceeds and falls into next page or pages in continuation then i want to "Rows to Repeat at Top " = $14:$14

Condition 2 : Suppose mydata range is from rows 15 to rows 57 with "TOTAL" at row 59 then I DONT WANT TO "Rows to Repeat at Top " = $14:$14
Eventhough datarange for Terms and Condtions have exceeded from 1st page onwards
Let me be also clear: Suppose if myData range exceeds more than 1 page or 2 pages then "Rows to Repeat " = $14:$14 but if the mydata Range ends in 2nd page then DONT WANT TO "Rows to Repeat" = $14:$14 on 3rd Page or more pages.

How to achieve the above result ?

Thanks
NimishK

 

[Maximus Tatius]

I think PrintTitleRows is what you should be looking at setting via your conditions in your VBA

 

[NimishK]

Ok Maximus

i know that PrintTitleRows is the Syntax used for "rows to Repeat" so how to achieve the above.

 

[Worf]

Hi

Please test this:

Sub PrintPages()
Dim r As Range, cp%, tp%
Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)
PageInfo r, cp, tp                          ' get page where table ends
With ActiveSheet
    .PageSetup.PrintTitleRows = ActiveSheet.Rows(14).Address
    .PrintOut from:=1, to:=cp               ' with header
    .PageSetup.PrintTitleRows = ""
    .PrintOut from:=cp + 1, to:=tp          ' no header
End With
End Sub


Sub PageInfo(actcell As Range, cp%, tp%)    ' based on code by Allen Wyatt
Dim iCol%, iCols%, lRows&, lRow&, x&, y&
tp = ExecuteExcel4Macro("Get.Document(50)")
ActiveWindow.View = xlPageBreakPreview
With ActiveSheet
    y = actcell.Column
    iCols = .VPageBreaks.Count
    x = 0
    Do
        x = x + 1
    Loop Until x = iCols Or y < .VPageBreaks(x).Location.Column
    iCol = x
    If y >= .VPageBreaks(x).Location.Column Then iCol = iCol + 1
    y = actcell.Row
    lRows = .HPageBreaks.Count
    x = 0
    Do
        x = x + 1
    Loop Until x = lRows Or y < .HPageBreaks(x).Location.Row
    lRow = x
    If y >= .HPageBreaks(x).Location.Row Then lRow = lRow + 1
    If .PageSetup.Order = xlDownThenOver Then
        cp = (iCol - 1) * (lRows + 1) + lRow
    Else
        cp = (lRow - 1) * (iCols + 1) + iCol
    End If
End With
End Sub

 

[NimishK]

will check and revert back Thanks

 

[NimishK]

Worf

Thanks

Checked in Excel2007 with the file attached above which then i renamed and saved as .xlsm
and put the above code as per yours and Allan Wyatt and below shown in thisWorkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Call PrintPages
End Sub

This is almost to what i desired
One Observation that When Print Previewing 2 Pages are seen by clicking NextPage Twice but to see the third page unfortunately i've to close PrintPreview and then it shows third Page Automatically. I dont know why it breaks while viewing it. Cant it be smooth Print_Previewing

for you to check pl find the attached
https://www.dropbox.com/s/eoe3m4liooqheby/Sample555-6-Sutures.xlsm?dl=0

Thanks and Regards
NimishK

 

[NimishK]

Tried to Run in Excel 2013 .It seems nothing is happening. Any syntax changed for above code in Excel 2013 ?

 

[Worf]

Hi

1) Using the before print event is not a good idea because the print out method will fire this event again. You could create a print button that calls the print pages macro.

2) It will be difficult to have a complete print preview in a single step because the header adjustment is done at run time. We could create an option to choose between the two sections and preview only that section, with or without a header.

' standard module
Sub PrintPages()
Dim r As Range, cp%, tp%
Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)
PageInfo r, cp, tp                          ' get page where table ends
With ActiveSheet
    .PageSetup.PrintTitleRows = ActiveSheet.Rows(14).Address
    .PrintOut from:=1, to:=cp               ' with header
    .PageSetup.PrintTitleRows = ""
    .PrintOut from:=cp + 1, to:=tp          ' no header
End With
ActiveWindow.View = xlNormalView
End Sub


Sub PageInfo(actcell As Range, cp%, tp%)    ' based on code by Allen Wyatt
Dim iCol%, iCols%, lRows&, lRow&, x&, y&
tp = ExecuteExcel4Macro("Get.Document(50)")
ActiveWindow.View = xlPageBreakPreview
With ActiveSheet
    y = actcell.Column
    iCols = .VPageBreaks.Count
    x = 0
    Do
        x = x + 1
    Loop Until x = iCols Or y < .VPageBreaks(x).Location.Column
    iCol = x
    If y >= .VPageBreaks(x).Location.Column Then iCol = iCol + 1
    y = actcell.Row
    lRows = .HPageBreaks.Count
    x = 0
    Do
        x = x + 1
    Loop Until x = lRows Or y < .HPageBreaks(x).Location.Row
    lRow = x
    If y >= .HPageBreaks(x).Location.Row Then lRow = lRow + 1
    If .PageSetup.Order = xlDownThenOver Then
        cp = (iCol - 1) * (lRows + 1) + lRow
    Else
        cp = (lRow - 1) * (iCols + 1) + iCol
    End If
End With
End Sub

 

[NimishK]

Thanks Worf for guiding me. Sorry for the late reply

This is very new to me
Implemented as per your suggestion. created another module for the same and this is what it happened

in Excel2013 (FYI Printer was not Connected at all) but it PrintPreviewed with PagebreakView but WITHOUT desired result

Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)
PageInfo r, cp, tp                          ' get page where table ends

it did not repeat:PrinttitlleRows as per the above range defined

in Excel2007(printer was connected and was ON)
It showed PrintPreview without the above desired reuslt and started Printing.

Can you help me with the above code only for print preview because
inorder to printpreveiw i removed the following '.PrintOut from:=1, to:=cp ' with header
.PrintOut from:=cp + 1, to:=tp ' no header
from the code below

' standard module
Sub PrintPages()
Dim r As Range, cp%, tp%

With ActiveSheet
    .PageSetup.PrintTitleRows = ActiveSheet.Rows(14).Address
    '.PrintOut from:=1, to:=cp         ' with header
    .PageSetup.PrintTitleRows = ""
    '.PrintOut from:=cp + 1, to:=tp   'no header
End With
ActiveWindow.View = xlNormalView
End Sub

Excel 2007 got Hanged but then i had to END Task the Excel Application
I want the Print Preview to be viewd First as desired as per post 1 and then Printing on Printer
no matter whichever excel version i use

attached file for your reference
https://www.dropbox.com/s/kjf6arxoi5hfx5o/Sample555-6R-Sutures.xlsm?dl=0


Thanks and Regards
NimishK

 

[Worf]

Hi

- This code does not account for all particular cases, but worked well with your sample file and Excel 2013.
- It assumes down then over page order. I will adapt it to over then down later.
- You can choose a section and it will be print previewed.
- Tag for googling: Excel worksheet page address.

Dim pag() As Range, ws As Worksheet


Sub PrintPreviewPages()                                 ' run this one
Dim r As Range, cp%, tp%, i%, section%, pp As Range, j%
section = 1                                             ' with title
section = 2                                             ' no title
ActiveWindow.View = xlPageBreakPreview
PageAddress
Set r = ActiveSheet.[b:b].Find("TOTAL", , xlValues)
i = 1
Do While Intersect(pag(i), r) Is Nothing
    i = i + 1
    If i > 50 Then Exit Do
Loop
Select Case section
    Case 1
        Set pp = pag(1)
        For j = 2 To i
            Set pp = Union(pp, pag(j))
        Next
        ws.PageSetup.PrintTitleRows = ws.Rows(14).Address
    Case 2
        Set pp = pag(i + 1)
        For j = i + 2 To UBound(pag)
            Set pp = Union(pp, pag(j))
        Next
        ws.PageSetup.PrintTitleRows = ""
End Select
pp.PrintPreview
ActiveWindow.View = xlNormalView
End Sub


Sub PageAddress()
Dim c%, v%, h%, cln%, rw%, hgth%, wth%
Set ws = ActiveSheet
c = 1
ReDim pag(1 To (ws.VPageBreaks.Count + 1) * (ws.HPageBreaks.Count + 1))     'all pages on that sheet
For v = 0 To ws.VPageBreaks.Count
    For h = 0 To ws.HPageBreaks.Count
        If v = ws.VPageBreaks.Count Then
            If v = 1 Then
                wth = 2 * ws.VPageBreaks(1).Location.Column - 2
            Else
                wth = 2 * ws.VPageBreaks(v).Location.Column - ws.VPageBreaks(v - 1).Location.Column - 1
            End If
        Else
            wth = ws.VPageBreaks(v + 1).Location.Column - 1
        End If
        If h = ws.HPageBreaks.Count Then
            hgth = 2 * ws.HPageBreaks(h).Location.Row - ws.HPageBreaks(h - 1).Location.Row - 1
        Else
            hgth = ws.HPageBreaks(h + 1).Location.Row - 1
        End If
        If v = 0 Then
            cln = 1
        Else
            cln = ws.VPageBreaks(v).Location.Column
        End If
        If h = 0 Then
            rw = 1
        Else
            rw = ws.HPageBreaks(h).Location.Row
        End If
        Set pag(c) = Range(Cells(rw, cln).Address & ":" & Cells(hgth, wth).Address)     ' page address
        c = c + 1
    Next
Next
End Sub