• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to return page number of a named cell

Q

qaliq

New Member
Hi everyone,


I have been trying to do this for about a month now however I keep getting stuck!


Is it possible to write a macro which would return the page number of a named cell and paste it as a value in an empty cell on another worksheet?


Thanks,
 
What exactly do you mean by page number? Do you want the name of the sheet?

The latter is easily accomplished using native formulas (start with using the CELL function), while the former is more difficult due to page sizes depending on printer settings and page layout.
 
Qaliq


Firstly, Welcome to the Chandoo.org Forum


Can I suggest that you explain what your trying to achieve and we'll see if there is other ways tom tackle your issue
 
Thanks for both your replies, much appreciated.


I want the actual page number(so based on the print settings that I have set)returning.


The reason that I want to do this is that I have a report in excel which has about 20 sections spread over 7 tabs. Each section is numbered and has a named cell at the start of it. I am now trying to create a table of contents at the beginning which will return the actual page nunmber (when printed) of each section. At the moment I am having to print the whole thing out with the page numbers in the footer and then manually type in the page numbers in my table of contents.


I can post a sample workbook if that would help?


Thanks,


Alice
 
The closest I was able to find was this using VB:

http://www.excelforum.com/showthread.php?t=387958&p=1034846&viewfull=1#post1034846


or this w/ formulas:

http://dailydoseofexcel.com/archives/2004/12/22/page-of-pages-in-a-cell/


Neither way seems very ideal, as they can work slowly. The other thing that's going to cause problems is the fact that you are printing multiple worksheets. That probably means that you're going to have to use a VB solution trigger by the Before_Print event macro.


How badly do you need this?
 
Last edited:
Hi, qaliq!


I think maybe it could be done. If it were possible it'd will work not only for named ranges but for every cell on a print selection.


Give a look at this file:

https://dl.dropboxusercontent.com/u... a named cell (for qaliq at chandoo.org).xlsm


I just filled a range of 500 rows by 100 columns with a row&col formula in 1st sheet, changed view to page view for easier control, and run below macro X to fill 2nd sheet with the cells after vertical page breaks in column A and horizontal page breaks in column B.


Code:

-----

Code: 
Option Explicit

Sub X()
Dim I As Integer, J As Integer
Worksheets(2).Cells.ClearContents
With Worksheets(1)
For I = 1 To .HPageBreaks.Count
Worksheets(2).Cells(I, 1).Value = .HPageBreaks(I).Location
Next I
For J = 1 To .VPageBreaks.Count
Worksheets(2).Cells(J, 2).Value = .VPageBreaks(J).Location
Next J
End With
End Sub
-----


With that you'd build in 3rd sheet a matrix to identify each cell of 1st sheet in which page will be printed. If that is what you're asking the next step is how to match that against the selected print area. If it's not, sorry but I misunderstood your requirement.


Just advise if any issue.


Regards!
 
Last edited by a moderator:
@All

Hi!


As an exercise I keep on playing with the workbook which now has 4 worksheets:

- 1st sheet, Original, is the original data

- 2nd sheet, Reference, is a helper worksheet with cells containing row & col references

- 3rd sheet, PageBreak, is where all the things happen after running macro, ending with a table with row/column of page breaks

- 4th sheet, PrintedAtPage, is the result, where each cell has the value of the page at which it'd be printed


Named ranges in worksheet PageBreak:


RowList: =DESREF(PageBreak!$A$2;0;0;CONTARA(PageBreak!$A:$A);1) -----> in english: =OFFSET(PageBreak!$A$2,0,0,COUNTA(PageBreak!$A:$A),1)


ColList: =DESREF(PageBreak!$B$2;0;0;CONTARA(PageBreak!$B:$B);1) -----> in english: =OFFSET(PageBreak!$B$2,0,0,COUNTA(PageBreak!$B:$B),1)


PrintOrderCell: =$H$1


RowColTable: =DESREF(PageBreak!$F$2;0;0;CONTARA(PageBreak!$A:$A)-1;CONTARA(PageBreak!$B:$B)-1) -----> in english: =OFFSET(PageBreak!$F$2,0,0,COUNTA(PageBreak!$A:$A)-1,COUNTA(PageBreak!$B:$B)-1)


RowListIndex: =DESREF(RowList;-1;;FILAS(RowList)+1,) -----> in english: =OFFSET(RowList,-1,ROWS(RowList)+1,)


ColListIndex: =DESREF(ColList;-1;;FILAS(ColList)+1,) -----> in english: =OFFSET(ColList,-1,ROWS(ColList)+1,)

The only code is:

-----
Code: 
Option Explicit

Sub GetPrintPageNumber()
'
' constants
Const ksWSOriginal = "Original"
Const ksWSInput = "Reference"
Const ksWSBreak = "PageBreak"
Const ksRow = "RowList"
Const ksCol = "ColList"
Const ksRowCol = "RowColTable"
Const ksPrintOrder = "PrintOrderCell"
Const ksWSPrinted = "PrintedAtPage"
'
' declarations
Dim rngRow As Range, rngCol As Range, rngRC As Range, rngOrder As Range
Dim I As Integer, J As Integer, K As Integer, L As Integer, M As Integer
Dim iOrder As Integer, bEndR As Boolean, bEndC As Boolean
'
' start
'  ranges
Set rngRow = Worksheets(ksWSBreak).Range(ksRow)
Set rngCol = Worksheets(ksWSBreak).Range(ksCol)
Set rngRC = Worksheets(ksWSBreak).Range(ksRowCol)
Set rngOrder = Worksheets(ksWSBreak).Range(ksPrintOrder)
'
' process
With Worksheets(ksWSInput)
    ' print order
    rngOrder.Cells(1, 1).Value = .PageSetup.Order
    iOrder = .PageSetup.Order
    ' row breaks
    rngRow.ClearContents
    For I = 1 To .HPageBreaks.Count
        rngRow.Cells(I).Value = .HPageBreaks(I).Location.Row - 1
    Next I
    rngRow.Cells(I).Value = .Rows.Count
    ' col breaks
    For I = 1 To .VPageBreaks.Count
        rngCol.Cells(I).Value = .VPageBreaks(I).Location.Column - 1
    Next I
    rngCol.Cells(I).Value = .Columns.Count
End With
With rngRC
    .ClearContents
    Select Case iOrder
    Case xlDownThenOver
        K = .Columns.Count
        L = .Rows.Count
    Case xlOverThenDown
        K = .Rows.Count
        L = .Columns.Count
    End Select
    M = 0
    bEndR = False
    For I = 1 To K
        If iOrder = xlOverThenDown And rngRow.Cells(I, 1).Value = "" Or _
        iOrder = xlDownThenOver And rngCol.Cells(I, 1).Value = "" Then
            bEndR = True
        Else
            bEndC = False
            For J = 1 To L
                If iOrder = xlDownThenOver And rngRow.Cells(J, 1).Value = "" Or _
                iOrder = xlOverThenDown And rngCol.Cells(J, 1).Value = "" Then
                    bEndC = True
                Else
                    M = M + 1
                    Select Case iOrder
                    Case xlOverThenDown
                        .Cells(I, J).Value = M
                    Case xlDownThenOver
                        .Cells(J, I).Value = M
                    End Select
                End If
                If bEndC Then Exit For
            Next J
        End If
        If bEndR Then Exit For
    Next I
End With
'
' end
'  ranges
Set rngOrder = Nothing
Set rngRC = Nothing
Set rngCol = Nothing
Set rngRow = Nothing
'  beep
Beep
'
End Sub
-----


Formulas:


a) 2nd sheet, Reference, for all used cells in 1st sheet Original:

="row."&FILA()&"_col."&COLUMNA() -----> in english: ="row."&ROW()&"_col."&COLUMN()


b) 3rd sheet, PageBreak:


Column A: horizontal page breaks (filled by macro)

Column B: vertical page breaks (filled by macro)


Cell C1: Original and Reference page print order, down/over or over/down (filled by macro)

Cell D1: max printed pages (Horizontal page breaks + 1) * (Vertical page breaks + 1)

=CONTAR(RowList)*CONTAR(ColList) -----> in english: =COUNT(RowList)*COUNT(ColList)


Column E, E2:Exx : RowBreak

=SI.ERROR(INDICE(RowList;FILA()-FILA($E1));"") -----> in english: =IFERROR(INDEX(RowList,ROW()-ROW($E1)),"")


Row 1, F1:yy1 : ColBreak (last column before page break)

=SI.ERROR(INDICE(ColList;COLUMNA()-COLUMNA($E1));"") ----->in english: =IFERROR(INDEX(ColList,COLUMN()-COLUMN($J1)),"")


Range F2:yyxx : RowColBreak (double entry table with print page for cells up to row & col) (filled by macro)


Link to the file (same previous link):

https://dl.dropboxusercontent.com/u... a named cell (for qaliq at chandoo.org).xlsm


If any interested, just drop a few lines.


Regards!


EDITED
 
Last edited by a moderator:
@Luke M

Hi!


I've never done such a thing before but if Excel is capable of superposing the page number on worksheets with print layout, why wouldn't it be possible to retrieve that value?


What I found very annoying is the property Location of the objects HPageBreaks(i) elements, I assumed it'll return an address or a print page number, but not, it retrieved the cell value! I think that the guy who wrote that code suffered from an excess of beer... of the cheapest one.


Next step might be automatizing the creation of Reference and PrintedAtPage worksheets from a given Original worksheet. Next to next might be the VTOC for a printed range or worsheets selection.


Regards!
 
@SirJB7


Indeed, it appears that location is actually a range object. You can do

Code: 
...HPageBreaks(1).Location.Address
if needed.

anyway, here's my crack at writing a function. Only works for one page so far.

Code: 
Function FindPage(r As Range) As Integer
'Function written by Luke M
'Input a single Range, outputs page that range is in
'Output is 0 if not in print range
Dim VCount As Long
Dim HCount As Long
Dim LastRow As Long
Dim LastCol As Long
Dim V()
Dim H()

With r.Worksheet
    'setup our boundary conditions
    VCount = .VPageBreaks.Count + 1
    HCount = .HPageBreaks.Count + 1
    LastRow = .UsedRange.Rows.Count + .UsedRange.Row
    LastCol = .UsedRange.Columns.Count + .UsedRange.Column
   
    If r.Row > LastRow Or r.Column > LastCol Then
        'Outside used range, won't print
        FindPage = 0
        Exit Function
    End If
   
    ReDim V(1 To VCount)
    ReDim H(1 To HCount)
   
    'Populate our arrays
    For I = 1 To VCount - 1
        V(I) = .VPageBreaks(I).Location.Column
    Next I
    For I = 1 To HCount - 1
        H(I) = .HPageBreaks(I).Location.Row
    Next I
   
    V(VCount) = LastCol
    H(HCount) = LastRow
   
    'Where does our cell fall?
    For x = 1 To VCount
        If r.Column < V(x) Then Exit For
    Next x
    For y = 1 To HCount
        If r.Row < H(y) Then Exit For
    Next y
   
    FindPage = y + (x - 1) * HCount

End With

End Function
 
Last edited:
@Luke M

Hi!

Thanks for the catch about Location, I should have read that it's a range object.

Nice function, will incorporate it. I'll go on with the automation of the whole process asap.

Regards!

PS: BTW, who said that it wasn't possible? :)


EDIT: instead of Address property I'm now using Row and Column properties and could eliminate 2 helper ranges, will re-upload when a bit more tweaked.
 
Thank you both for your replies. I am going to have a go at incorporating some of your ideas into my report and I will get back if I have any issues or (even better) if I manage to get a fully working solution.


Much appreciated.


Alice
 
Hi, qaliq!


If I don't misunderstand your requirement, you might create a new worksheet and place there a list of the cells and named ranges that you want to find out in which page they'd be printed and perform a search (Index, Match, ...) against the Reference and PrintedAtPage worksheets. I didn't test it with named ranges, will be posting here if any update.


Regards!


PS: I updated the my previous post with the updated (and simplified) version of the uploaded file after following Luke M's suggestions. Please check the edited previous post and download again the file from same previous link.

http://chandoo.org/forum/threads/macro-to-return-page-number-of-a-named-cell.9787/#post-56033
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top