Excel/Page no. in Roman

Advertisement


Question
Sir,

how can i put roman page number in all the sheets of my workbook in sequence?
For example:
in sheet1: I, II, III, IV then on sheet2: V, VI, VII, VIII, IX, on sheet3; X, XI, XII and so on to all the pages of the sheets in the workbook.

I want to achieve this using macro that will put roman page numbers and then the macro save the workbook.

Answer
Hello Jack,

I'm assuming you want to accomplish this by placing the page numbering in the sheet footers.  Unfortunately there is not a way to put a different footer on each page of a worksheet in Excel because Excel doesn't have a footer object for each page--it only has a separate first page footer and subsequent pages footer.  That being said however, you can accomplish very nearly what you describe using the following macro. It prints all the pages in the workbook placing the roman numeral page numbers in the center footer.  But it does this by creating the roman page numbers "on the fly", i.e., creating the footer just before printing each page.  Here's the code:

___________________________________________

Sub RomanPageNums()
  'prints all pages of all worksheets, sequentially numbering pages with
  'roman numerals in center footer.
  Dim nPgs       As Integer
  Dim iPage      As Integer   'running count of pages
  Dim iPg        As Integer   'count of pages within active sheet
  Dim iSheet     As Integer

  iPage = 0
  
  For iSheet = 1 To Sheets.Count
    
    Sheets(iSheet).Activate

    ' Get count of pages in active sheet
    nPgs = ExecuteExcel4Macro("Get.Document(50)")
  
  ' Print worksheet, page by page
    With Sheets(iSheet)
        For iPg = 1 To nPgs
         iPage = iPage + 1
         ' Set page footer in roman numerals
         .PageSetup.CenterFooter = Application.Roman(iPage)
         ' Print page iPg
         .PrintOut From:=iPg, To:=iPg
        Next iPg
    End With
  
  Next iSheet
  
  'ThisWorkbook.Save   'uncomment this line to automatically save workbook
  
End Sub
_______________________________________________________________

If you want the workbook saved upon completion of the macro simply uncomment (remove the leading apostrophe) the ThisWorkbook.Save line. I commented it out since I didn't think you would want it since the macro doesn't actually place a different footer on each page.

I hope you find this helpful.  Feel free to follow up if you have any questions.

Damon  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.