Excel/Page no. in Roman
how can i put roman page number in all the sheets of my workbook in sequence?
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.
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:
'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
' Get count of pages in active sheet
nPgs = ExecuteExcel4Macro("Get.Document(50)")
' Print worksheet, page by page
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
'ThisWorkbook.Save 'uncomment this line to automatically save workbook
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.