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.
