Page Numbers/Names listed on a Summary sheet?

D

Derrick

Hello!
Im curious to find out if it's possible to insert the page numbers & sheet
names of other sheets into a table which summarizes all the sheets.
for example:
summary sheet:
page # Sheet Name ...Other Calculated numbers...
1 sheet1 ###
2-3 sheet2 ###
4 sheet3 ###
5-10 sheet4 ###

where: sheet1 will print out on 1 page, sheet2 on 2 pages, etc. - depending
on page breaks

hopefully this won't include me manually inserting any numbers or names.

Any help?
Thanks,
 
R

Rick Rothstein

Give this macro a try (it assumes your summary sheet is named Summary and
that the page numbers will go in Column A and the worksheet names in Column
B starting in Row 2)...

Sub PagesOnSheets()
Dim WS As Worksheet
Dim RowPosition As Long
On Error GoTo Whoops
Application.EnableEvents = False
Application.ScreenUpdating = False
RowPosition = 0
For Each WS In Worksheets
WS.Activate
If WS.Name <> "Summary" Then
Worksheets("Summary").Range("A2").Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets("Summary").Range("B2").Offset(RowPosition).Value = WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
 
D

Derrick

hi Rick,

thank you for your help!

however... i very seldom use macros, so i'm not sure what to do with this.

can you help me with what to do with this?

also, can you direct where i would change the rows and columns to display
the page numbers?

thanks again
 
R

Rick Rothstein

Okay, I have modified the code (see below) so that you can easily change
things as needed (see description after the code). To install this new code,
press Alt+F11 to get into the VB editor. Once in the VB editor, click
Insert/Module on its menu bar. This will open up a code window... just
copy/paste the code below into that code window...

Sub PagesOnSheets()
Const StartingRowForList As Long = 5
Const NumberOfPagesColumn As String = "J"
Const WorksheetNameColumn As String = "M"
Const SummaryWorkSheetName As String = "Summary"
Dim WS As Worksheet
Dim RowPosition As Long
On Error GoTo Whoops
Application.EnableEvents = False
Application.ScreenUpdating = False
RowPosition = 0
For Each WS In Worksheets
WS.Activate
If WS.Name <> SummaryWorkSheetName Then
Worksheets(SummaryWorkSheetName).Range(NumberOfPagesColumn & _
StartingRowForList).Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets(SummaryWorkSheetName).Range(WorksheetNameColumn & _
StartingRowForList).Offset(RowPosition).Value = WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Okay, once you have copied the above code into the Module's code window,
locate the first 4 lines in the procedure (they all start with the keyword
Const). You can change these by changing the values after the equal sign (if
an item is shown with quotes around it, your changed value must also have
quotes around it... this note applies to the last three Const statements).
The names after the Const statements should be self-explanatory as to what
parameter they control... just change the values to the values you want them
to have. After you have done all of the above, the macro will be available
for you to run by pressing Alt+F8... just select the PagesOnSheets item from
the ListBox and click the Run button. The macro operates on the active
worksheet, so go to that worksheet BEFORE you press Alt+F8 to run the macro.
 
D

Dave Peterson

FYI:

You don't need to activate the worksheet to get the pages. The Get.document()
macro can take a second parm.

.... = Application.ExecuteExcel4Macro("get.document(50,""" & ws.Name & """)")
 
R

Rick Rothstein

Ah, having hardly ever used Excel4 Macros before, I didn't realize that...
Thanks!

Derrick... here is the code I posted earlier, modified to implement Dave's
simplification (you would still change the assigned value in the Const
statements as I described earlier)...

Sub PagesOnSheets()
Const StartingRowForList As Long = 2
Const NumberOfPagesColumn As String = "A"
Const WorksheetNameColumn As String = "B"
Const SummaryWorkSheetName As String = "Summary"
Dim WS As Worksheet
Dim RowPosition As Long
On Error GoTo Whoops
Application.EnableEvents = False
Application.ScreenUpdating = False
RowPosition = 0
For Each WS In Worksheets
If WS.Name <> SummaryWorkSheetName Then
Worksheets(SummaryWorkSheetName).Range(NumberOfPagesColumn & _
StartingRowForList).Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50,""" & WS.Name & """)")
Worksheets(SummaryWorkSheetName).Range(WorksheetNameColumn & _
StartingRowForList).Offset(RowPosition).Value = WS.Name
RowPosition = RowPosition + 1
End If
Next
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top