Specific Sheet Page Number in "Index" cell

[klarowe]

Is there a way that I can enter a cells value as a specific sheet's workbook page number (as printed/viewed on footer)? It is an index for this inspection report and through check-boxes I am going to be hiding certain sheets that are no applicable to a specific inspection. Therefore as the sheet is hidden, I need the page numbers for the index to change so that they remain correct to the workbook page number of that sheet... if that makes any sense.

Thanks in advance.

Kenny

Cross posted reference: http://www.thecodecage.com/forumz/m...l-workbook-sheets-page-number.html#post744040
My boss wants this project completed so I need to find an answer as soon as possible. This is pretty much the last hurdle I need to overcome to finish it up.

 

[pbornemeier]

The first procedure uses cells on a sheet to set the footer, the second adds a Page X of Y footer, where X and Y only reference visible worksheets.

Option Explicit
Sub PutCellValueIntoFooter()
    'A1 contains the page #
    'A2 containts the total number of pages
    With ActiveSheet.PageSetup
        .RightFooter = "Page " & Range("A1").Value & " of " & Range("A2").Value
    End With
End Sub
 
Sub SequentiallyNumberVisiblePagesOnly()
    Dim lx As Long
    Dim lVisibleSheets As Long
    Dim lVisibleCount As Long
    For lx = 1 To Worksheets.Count
        If Worksheets(lx).Visible = True Then lVisibleSheets = lVisibleSheets + 1
    Next
    For lx = 1 To Worksheets.Count
        If Worksheets(lx).Visible = True Then lVisibleCount = lVisibleCount + 1
        With Worksheets(lx).PageSetup
            .RightFooter = "Page " & lVisibleCount & " of " & lVisibleSheets
        End With
    Next
End Sub

 

[klarowe]

That looks like it got pretty close, but what I need it to do is actually change the value in a certain cell to match the footer page number.
Its an index and I want it to automatically re-number itself if a sheet is hidden.

Example:
All Visible:
1 - Sheet A
2 - Sheet B
3 - Sheet C
4 - Sheet D
5 - Sheet E

If Sheet C and D are hidden I would like it to show up:
1 - Sheet A
2 - Sheet B
- Sheet C
- Sheet D
3 - Sheet E

 

[pbornemeier]

If the actual page footer number is manually entered in a known section of the footer in each page, the it could be extracted for each visible sheet.

The following code uses a different method. See if you can adapt this to do what you want

Sub GenerateIndexBasedOnVisibleSheets()
    Dim lX As Long
    Dim sIndex As String
    Dim sIndexStartRange As String
    Dim lVisibleCount As Long
 
    sIndex = "Index"
    sIndexStartRange = "B4"
    'Use something like the following line to clear the index range
    'Worksheets(sIndex).Range(sIndexStartRange).CurrentRegion.Cells.Clear
 
    With Worksheets(sIndex).Range("B4")
        .Resize(1, 2) = Array("Page Number", "Worksheet Name")
 
        For lX = 1 To Worksheets.Count
            Select Case Worksheets(lX).Name
            Case sIndex
                'do nothing
            Case Else
                If Worksheets(lX).Visible = True Then
                    lVisibleCount = lVisibleCount + 1
                    'uncomment next 2 lines to show only visible sheets/number in index
                    '.Offset(lVisibleCount, 0) = lVisibleCount
                    '.Offset(lVisibleCount, 1) = Worksheets(lX).Name
                End If
                'uncomment next 2 lines show all sheet names, only visible numbers
                If Worksheets(lX).Visible = True Then .Offset(lX - 1, 0) = lVisibleCount
                .Offset(lX - 1, 1) = Worksheets(lX).Name
            End Select
        Next
    End With
End Sub

 

[klarowe]

I sent you a couple PM's and since sending the last one have continued to play around and I have just about everything figured out with this code modification:

Sub GenerateIndexBasedOnVisibleSheets()
    Dim lX As Long
    Dim sIndex As String
    Dim sIndexStartRange As String
    Dim lVisibleCount As Long
 
    sIndex = "Index"
    sIndexStartRange = "A7"
 
    Worksheets(sIndex).Range("A7:A60").Cells.ClearContents
    With Worksheets(sIndex).Range("A7")
 
        For lX = 1 To Worksheets.Count
            Select Case Worksheets(lX).Name
            'uncomment next line to skip Index line
            'Case sIndex
                'do nothing
            Case Else
                If Worksheets(lX).Visible = True Then
                    lVisibleCount = lVisibleCount + 1
                    'uncomment next 2 lines to show only visible sheets/number in index
                    '.Offset(lVisibleCount, 0) = lVisibleCount
                    '.Offset(lVisibleCount, 1) = Worksheets(lX).Name
                End If
                'uncomment next 2 lines show all sheet names, only visible numbers
                If Worksheets(lX).Visible = True Then .Offset(lX - 1, 0) = lVisibleCount
                .Offset(lX - 1, 1) = Worksheets(lX).Name
            End Select
        Next
    End With
End Sub

The only issue I have left is that when I run the macro, it is changing the names in Column B to match the names of each sheet. Is there a way to leave Column B as it is and only make changes to Column A?? If not I can just rename each sheet as I want it to appear in the index and change my other codes accordingly.

Thanks again!!

Edit: I just noticed one other small issue. If a single sheet has multiple pages, it is throwing off the index. Is there a way to add that to the code, or do I have to do a page setup code for numbering pages instead of adding "&[Page] to each sheet footer?

 

[pbornemeier]

This might do it:

Sub GenerateIndexBasedOnVisibleSheets()
    Dim lX As Long
    Dim sIndex As String
    Dim sIndexStartRange As String
    Dim lVisiblePageCount As Long
 
    sIndex = "Index"
    sIndexStartRange = "A7"
 
    Worksheets(sIndex).Range("A7:A60").Cells.ClearContents
    With Worksheets(sIndex).Range("A7")
 
        For lX = 1 To Worksheets.Count
            Select Case Worksheets(lX).Name
            'uncomment next line to skip Index line
            'Case sIndex
                'do nothing
            Case Else
                If Worksheets(lX).Visible = True Then
                    lVisiblePageCount = lVisiblePageCount + 1
                    'uncomment next 2 lines to show only visible sheets/number in index
                    '.Offset(lVisiblePageCount, 0) = lVisiblePageCount
                    '.Offset(lVisiblePageCount, 1) = Worksheets(lX).Name
                End If
                'uncomment next 2 lines show all sheet names, only visible numbers
                If Worksheets(lX).Visible = True Then .Offset(lX - 1, 0) = lVisiblePageCount
                '.Offset(lX - 1, 1) = Worksheets(lX).Name 'prints sheet name
                If Worksheets(lX).Visible = True Then
                    'Compensates if more than 1 printed page per worksheet
                    lVisiblePageCount = lVisiblePageCount - 1 + Worksheets(lX).HPageBreaks.Count
                End If
            End Select
        Next
    End With
End Sub

 

[pbornemeier]

Change this line:
lVisiblePageCount = lVisiblePageCount - 1 + Worksheets(lX).HPageBreaks.Count
to this:
lVisiblePageCount = lVisiblePageCount + Worksheets(lX).HPageBreaks.Count

 

[klarowe]

Unfortunately that last change didnt help. It just numbered every sheet "1". But I was able to modify the code you posted above and made it work, so everything is working 100%. Might now be the most simplified, but it works and thats what matters.
Thanks again for the help!!

Edit:
For future reference, here are the final codes that made it work:

Sub SequentiallyNumberVisiblePagesOnly()
    Dim lx As Long
    Dim lVisibleSheets As Long
    'Dim lVisibleCount As Long
   ' For lx = 1 To Worksheets.Count
       'If Worksheets(lx).Visible = True Then lVisibleSheets = lVisibleSheets + 1
   ' Next
    For lx = 1 To Worksheets.Count
        If Worksheets(lx).Visible = True Then lVisibleCount = lVisibleCount + 1
        With Worksheets(lx).PageSetup
            .RightFooter = lVisibleCount
        End With
    Next
End Sub
Sub IndexNumber()
    Dim lx As Long
    Dim sIndex As String
    Dim sIndexStartRange As String
    Dim lVisibleCount As Long
 
    sIndex = "Index"
    sIndexStartRange = "A7"
    
    Worksheets(sIndex).Range("A7:A60").Cells.ClearContents
    With Worksheets(sIndex).Range("A7")
 
        For lx = 1 To Worksheets.Count
            Select Case Worksheets(lx).Name
            'uncomment next line to skip Index line
            'Case sIndex
                'do nothing
            Case Else
                If Worksheets(lx).Visible = True Then
                    lVisibleCount = lVisibleCount + 1
                    'uncomment next 2 lines to show only visible sheets/number in index
                    '.Offset(lVisibleCount, 0) = lVisibleCount
                    '.Offset(lVisibleCount, 1) = Worksheets(lX).Name
                End If
                'uncomment next 2 lines show all sheet names, only visible numbers
                If Worksheets(lx).Visible = True Then .Offset(lx - 1, 0) = lVisibleCount
                '.Offset(lx - 1, 1) = Worksheets(lx).Name '<-- prints sheet name
            End Select
        Next
    End With
End Sub