I have a workbook containing several sheets; all with varied amounts of data.
What I would like to do is to create a dynamic summary page of all the
sheets in the workbook, where all the cells are live links to their original
locations.

Any ideas?

Thanks

This should get you started.
sub ABC()
Dim sh as Worksheet, sh1 as Worksheet
Dim rw as Long
rw = 2
set sh1 = worksheets("Summary")
for each sh in worksheets
if sh.Name < sh1.Name then
sh1.Cells(rw,"A").Value = sh.Name
sh1.Cells(rw,"B").Formula = "=" & sh.Range( _
"B9").Address(0,0,xlA1,True)
rw = rw + 1
end if
Next
end Sub

Expand to meet your needs.

--
Regards,
Tom Ogilvy



"billinr" wrote:

I have a workbook containing several sheets; all with varied amounts of data.
What I would like to do is to create a dynamic summary page of all the
sheets in the workbook, where all the cells are live links to their original
locations.

Any ideas?

Thanks

Thanks for the start, Tom.
Please forgive my ignorance, but this doesn't quite provide all the
information. The result I see for cell B9 is good, but how do I change that
to show the entire used range of the sheet? I would need to see all of the
information on all of the sheets in the summary.

Thanks again for your help.

"Tom Ogilvy" wrote:

This should get you started.
sub ABC()
Dim sh as Worksheet, sh1 as Worksheet
Dim rw as Long
rw = 2
set sh1 = worksheets("Summary")
for each sh in worksheets
if sh.Name < sh1.Name then
sh1.Cells(rw,"A").Value = sh.Name
sh1.Cells(rw,"B").Formula = "=" & sh.Range( _
"B9").Address(0,0,xlA1,True)
rw = rw + 1
end if
Next
end Sub

Expand to meet your needs.

--
Regards,
Tom Ogilvy



"billinr" wrote:

I have a workbook containing several sheets; all with varied amounts of data.
What I would like to do is to create a dynamic summary page of all the
sheets in the workbook, where all the cells are live links to their original
locations.

Any ideas?

Thanks

Sub ABC()
Dim sh As Worksheet, sh1 As Worksheet
Dim rw As Long, rng As Range
rw = 1
Set sh1 = Worksheets("Summary")
sh1.Activate
For Each sh In Worksheets
If sh.Name < sh1.Name Then
Set rng = sh.UsedRange
sh1.Cells(rw, "A").Select
rng.Copy
sh1.Paste link:=True
rw = rw + rng.Rows.Count
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"billinr" wrote:

Thanks for the start, Tom.
Please forgive my ignorance, but this doesn't quite provide all the
information. The result I see for cell B9 is good, but how do I change that
to show the entire used range of the sheet? I would need to see all of the
information on all of the sheets in the summary.

Thanks again for your help.

"Tom Ogilvy" wrote:

This should get you started.
sub ABC()
Dim sh as Worksheet, sh1 as Worksheet
Dim rw as Long
rw = 2
set sh1 = worksheets("Summary")
for each sh in worksheets
if sh.Name < sh1.Name then
sh1.Cells(rw,"A").Value = sh.Name
sh1.Cells(rw,"B").Formula = "=" & sh.Range( _
"B9").Address(0,0,xlA1,True)
rw = rw + 1
end if
Next
end Sub

Expand to meet your needs.

--
Regards,
Tom Ogilvy



"billinr" wrote:

I have a workbook containing several sheets; all with varied amounts of data.
What I would like to do is to create a dynamic summary page of all the
sheets in the workbook, where all the cells are live links to their original
locations.

Any ideas?

Thanks

Thanks for your help, Tom.

With a little customization, I am able to get the results I need.

I really appreciate your time.

"Tom Ogilvy" wrote:

Sub ABC()
Dim sh As Worksheet, sh1 As Worksheet
Dim rw As Long, rng As Range
rw = 1
Set sh1 = Worksheets("Summary")
sh1.Activate
For Each sh In Worksheets
If sh.Name < sh1.Name Then
Set rng = sh.UsedRange
sh1.Cells(rw, "A").Select
rng.Copy
sh1.Paste link:=True
rw = rw + rng.Rows.Count
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"billinr" wrote:

Thanks for the start, Tom.
Please forgive my ignorance, but this doesn't quite provide all the
information. The result I see for cell B9 is good, but how do I change that
to show the entire used range of the sheet? I would need to see all of the
information on all of the sheets in the summary.

Thanks again for your help.

"Tom Ogilvy" wrote:

This should get you started.
sub ABC()
Dim sh as Worksheet, sh1 as Worksheet
Dim rw as Long
rw = 2
set sh1 = worksheets("Summary")
for each sh in worksheets
if sh.Name < sh1.Name then
sh1.Cells(rw,"A").Value = sh.Name
sh1.Cells(rw,"B").Formula = "=" & sh.Range( _
"B9").Address(0,0,xlA1,True)
rw = rw + 1
end if
Next
end Sub

Expand to meet your needs.

--
Regards,
Tom Ogilvy



"billinr" wrote:

I have a workbook containing several sheets; all with varied amounts of data.
What I would like to do is to create a dynamic summary page of all the
sheets in the workbook, where all the cells are live links to their original
locations.

Any ideas?

Thanks