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 ###

whe 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,

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(RowPositi on).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets("Summary").Range("B2").Offset(RowPositi on).Value = WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Derrick" wrote in message
...
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 ###

whe 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,

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

"Rick Rothstein" wrote:

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(RowPositi on).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets("Summary").Range("B2").Offset(RowPositi on).Value = WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Derrick" wrote in message
...
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 ###

whe 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,

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(NumberOfPag esColumn & _
StartingRowForList).Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets(SummaryWorkSheetName).Range(WorksheetNa meColumn & _
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.

--
Rick (MVP - Excel)


"Derrick" wrote in message
...
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

"Rick Rothstein" wrote:

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(RowPositi on).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets("Summary").Range("B2").Offset(RowPositi on).Value =
WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Derrick" wrote in message
...
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 ###

whe 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,

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 & """)")



Rick Rothstein wrote:

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(NumberOfPag esColumn & _
StartingRowForList).Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets(SummaryWorkSheetName).Range(WorksheetNa meColumn & _
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.

--
Rick (MVP - Excel)

"Derrick" wrote in message
...
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

"Rick Rothstein" wrote:

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(RowPositi on).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets("Summary").Range("B2").Offset(RowPositi on).Value =
WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Derrick" wrote in message
...
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 ###

whe 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,



--

Dave Peterson

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(NumberOfPag esColumn & _
StartingRowForList).Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50,""" & WS.Name & """)")
Worksheets(SummaryWorkSheetName).Range(WorksheetNa meColumn & _
StartingRowForList).Offset(RowPosition).Value = WS.Name
RowPosition = RowPosition + 1
End If
Next
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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 &
""")")



Rick Rothstein wrote:

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(NumberOfPag esColumn & _
StartingRowForList).Offset(RowPosition).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets(SummaryWorkSheetName).Range(WorksheetNa meColumn & _
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.

--
Rick (MVP - Excel)

"Derrick" wrote in message
...
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

"Rick Rothstein" wrote:

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(RowPositi on).Value = _
ExecuteExcel4Macro("GET.DOCUMENT(50)")
Worksheets("Summary").Range("B2").Offset(RowPositi on).Value =
WS.Name
RowPosition = RowPosition + 1
End If
Next
Worksheets("Summary").Activate
Whoops:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


"Derrick" wrote in message
...
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 ###

whe 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,



--

Dave Peterson