Page Numbers & Table of Contents

[DragonWood]

Hi,

I need some help with page numbers in Excel 2010. I actually have three problems I’m trying to solve, but they all fall under this. Any help with any of the problems would be great.


I have several worksheets in my workbook, but only need to print some of them.


I have a “Table of Contents” page that lists the worksheets I need to print.


Problem 1:
The page numbers need to increment in a specific order; meaning that if Sheet 1 ends on page 5, then Sheet 2 starts on page 6.
The sheets are not in the same order within the workbook that they need to increment in (currently, they are in alphabetical order). I can move them if that’s all it takes to make this work, I’m just hoping I don’t need to.

Problem 2:
I want the TOC to automatically fill in the page numbers for the pages I want to print.

Problem 3:
I want to have a button that will print all the pages that need printed, in the order they need printed (starting with the TOC).

Again, any help you can provide would be greatly appreciated.

 

[DragonWood]

I apologize for bumping this, but I really need some help. I have looked searched this forum and can't find anything similar to what I'm trying to do. All I can find anywhere else is just how to add the page numbers and if I were printing each worksheet separately, how to set the page number when printing it.

Thanks.

 

[DragonWood]

Ok, I was able to create write some code that puts the page numbers in the TOC (that’s at the bottom).

I still am having problems finding out how to make the page number that displays on the page increment. Included in the code that does increment the pages for the TOC display is what I thought might work for incrementing the pages too, but it’s not working. That part is:

  With Sheets("Group Summary").PageSetup.FirstPageNumber = pgsWS + 1

This code, I thought would take the numbers it gets for the TOC and put it as the page number for the designated sheet. Am I missing a step? Or am I way off track here?

The code I made uses the Print Preview to calculate the page numbers. I included a message box that tells the user they will need to manually close each preview in order to calculate the page numbers.

Is there a way to make that close automatically? I would prefer it that way.

Unless I can make the page numbers show the way I want on each Worksheet and I can make the Print Preview page close automatically; making the reports so they will print in order would be mote, if someone knows how to make the print thing happen, I might be able to manipulate that to make the rest work too.
Here is the code for my TOC:

  Public Sub UpdateTOC(control As IRibbonControl)
  'Created Dragon Wood (September, 2010)
  'Updates the table numbers in the TOC
   
      Dim shtWS As Worksheet 'Work Summary
      Dim pgsWS As Integer
      Dim shtGS As Worksheet 'Group Summary
      Dim pgsGS As Integer
      Dim shtDL As Worksheet 'Daily Log of Operations
      Dim pgsDL As Integer
      Dim shtBR As Worksheet 'Bunch Record
      Dim pgsBR As Integer
      Dim shtMD As Worksheet 'Maintenance Record
      Dim pgsMD As Integer
      Dim shtFR As Worksheet 'Formation Record
      Dim pgsFR As Integer
      Dim shtDR As Worksheet 'Distance Record
      Dim pgsDR As Integer
      Dim shtTG As Worksheet 'Total Groups
      Dim pgsTG As Integer
      Dim shtSV As Worksheet 'Surveys
      Dim pgsSV As Integer
      Dim shtSD As Worksheet 'Sample Descriptions
      Dim pgsSD As Integer
      Dim shtDS As Worksheet 'Distribution of Records
      Dim pgsDS As Integer
      Dim shtTC As Worksheet 'Table of Contents
      Dim pgsTC As Integer
      Dim Msg As String
      Dim Title As String
      Dim Config As Integer
      Dim Ans As Integer
      
      
  'Performs a print preview on each page so Excel can calculate the page breaks
  'The User must close each Preview page so the next can be opened. The message box explains that.
      Msg = "Excel needs to open a Print Preview window to calculate the page numbers."
      Msg = Msg & vbNewLine & "Please close each window so the calculations can complete."
      Msg = Msg & vbNewLine & "There are 11 windows total."
      Title = "Calculate The Page Numbers"
      Config = vbOKOnly & vbInformation
      Ans = MsgBox(Msg, Config, Title)
          
  'Gather the information about the Work Summary and enter the information in the TOC
      Application.Goto Sheets("Work Summary").Range("A1"), True
      With Sheets("Work Summary")
         ActiveWindow.SelectedSheets.PrintPreview
          WSHPages = ActiveSheet.HPageBreaks.Count + 1
          WSVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsWS = WSHPages * WSVPages
      End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H13").Value = PageCount + 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Geologic Summary and enter the information in the TOC
      Application.Goto Sheets("Group Summary").Range("A1"), True
      With Sheets("Group Summary").PageSetup.FirstPageNumber = pgsWS + 1
         ActiveWindow.SelectedSheets.PrintPreview
          GSHPages = ActiveSheet.HPageBreaks.Count + 1
          GSVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsGS = GSHPages * GSVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H15").Value = PageCount + pgsWS - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Daily Log of Operations and enter the information in the TOC
      Application.Goto Sheets("Daily Log Of Operations").Range("A1"), True
      With Sheets("Daily Log Of Operations").PageSetup.FirstPageNumber = pgsGS + 1
         ActiveWindow.SelectedSheets.PrintPreview
          DLHPages = ActiveSheet.HPageBreaks.Count + 1
          DLVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsDL = DLHPages * DLVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H17").Value = PageCount + pgsWS + pgsGS - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Bunch Record and enter the information in the TOC
      Application.Goto Sheets("Bunch Record").Range("A1"), True
      With Sheets("Bunch Record").PageSetup.FirstPageNumber = pgsBR + 1
         ActiveWindow.SelectedSheets.PrintPreview
          BRHPages = ActiveSheet.HPageBreaks.Count + 1
          BRVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsBR = BRHPages * BRVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H19").Value = PageCount + pgsWS + pgsGS + pgsBR - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Maintenance Record and enter the information in the TOC
      Application.Goto Sheets("Maintenance Record").Range("A1"), True
      With Sheets("Maintenance Record").PageSetup.FirstPageNumber = pgsMD + 1
         ActiveWindow.SelectedSheets.PrintPreview
          MDHPages = ActiveSheet.HPageBreaks.Count + 1
          MDVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsMD = MDHPages * MDVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H21").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Formation Record and enter the information in the TOC
      Application.Goto Sheets("Formation Record").Range("A1"), True
      With Sheets("Formation Record").PageSetup.FirstPageNumber = pgsFR + 1
         ActiveWindow.SelectedSheets.PrintPreview
          FRHPages = ActiveSheet.HPageBreaks.Count + 1
          FRVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsFR = FRHPages * FRVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H23").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD + pgsFR - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Distance Record and enter the information in the TOC
      Application.Goto Sheets("Distance Record").Range("A1"), True
      With Sheets("Distance Record").PageSetup.FirstPageNumber = pgsDR + 1
         ActiveWindow.SelectedSheets.PrintPreview
          DRHPages = ActiveSheet.HPageBreaks.Count + 1
          DRVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsDR = DRHPages * DRVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H25").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD + pgsFR + pgsDR - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Total Groups and enter the information in the TOC
      Application.Goto Sheets("Total Groups").Range("A1"), True
      With Sheets("Total Groups").PageSetup.FirstPageNumber = pgsTG + 1
         ActiveWindow.SelectedSheets.PrintPreview
          TGHPages = ActiveSheet.HPageBreaks.Count + 1
          TGVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsTG = TGHPages * TGVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H27").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD + pgsFR + pgsDR + pgsTG - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Surveys and enter the information in the TOC
      Application.Goto Sheets("Surveys").Range("A1"), True
      With Sheets("Surveys").PageSetup.FirstPageNumber = pgsSV + 1
         ActiveWindow.SelectedSheets.PrintPreview
          SVHPages = ActiveSheet.HPageBreaks.Count + 1
          SVVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsSV = SVHPages * SVVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H29").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD + pgsFR + pgsDR + pgsTG + pgsSV - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Sample Descriptions and enter the information in the TOC
      Application.Goto Sheets("Sample Descriptions").Range("A1"), True
      With Sheets("Sample Descriptions").PageSetup.FirstPageNumber = pgsSD + 1
         ActiveWindow.SelectedSheets.PrintPreview
          SDHPages = ActiveSheet.HPageBreaks.Count + 1
          SDVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsSD = SDHPages * SDVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H31").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD + pgsFR + pgsDR + pgsTG + pgsSV + pgsSD - 1
          .Protect Password:="Password"
      End With
      
  'Gather the information about the Distribution of Records and enter the information in the TOC
      Application.Goto Sheets("Distribution of Records").Range("A1"), True
      With Sheets("Distribution of Records").PageSetup.FirstPageNumber = pgsDS + 1
         ActiveWindow.SelectedSheets.PrintPreview
          DSHPages = ActiveSheet.HPageBreaks.Count + 1
          DSVPages = ActiveSheet.VPageBreaks.Count + 1
          pgsDS = DSHPages * DSVPages
    End With
      
      Application.Goto Sheets("Table Of Contents").Range("A1"), True
      With Sheets("Table Of Contents")
          .Unprotect Password:="Password"
          .Range("H33").Value = PageCount + pgsWS + pgsGS + pgsBR + pgsMD + pgsFR + pgsDR + pgsTG + pgsSV + pgsSD + pgsDS - 1
          .Protect Password:="Password"
      End With
          
  End Sub

I appreciate any help I can get with this.