Thanks for the code example, it is exactly the elegant solution I am looking
for.

Apart from that, I'll take with me two other lessons:

~ Always post in plain text
~ Learn to 'Dim' my variables as good practice.

Regards,

Neil


"JE McGimpsey" wrote in message
...
While I won't bother to try to decipher all of your manual sub, since it
was pasted with no linefeeds, the solution is not to Select/Activate
worksheets at all in that sub. Deal directly with the range references -
it's faster, and smaller code, and IMO, easier to maintain.

Perhaps something like:

Dim ws As Worksheet
Dim rDest As Range

Set rDest = Sheets("Index").Cells(5, 2)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "Index" Then
rDest.Value = ws.Cells(27, 2).Value
rDest.Offset(0, 1).Value = ws.Cells(2, 15).Value
Set rDest = rDest.Offset(1, 0)
End If
Next ws

Instead of In article , "Neil"
wrote:

Hi All,

I have utilised the Worksheets Index macro from ozgrid.com as follows:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1

With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name < Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start " & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start " & wSheet.Index,
TextToDisplay:=wSheet.Name
End If
Next wSheet
End SubThis is a great macro that auto updates and builds a TOC every
time I
open (activate) the "Index" sheet.Unfortunately I'd like to also be able
to
run another macro (manually, press 'button') from within this
worksheet.The
macro cycles through the available worksheets in the workbook and copies
across data, thus creating a summary of key fields in my worksheets, as
follows:Sub Transfer_Index_Summary()'' Transfer_Index_Summary Macro'
Macro
recorded 3/07/2007 by Neil'Application.ScreenUpdating = Falsetotalsheets
=
Worksheets.CountRange("b5:f16").ClearContentssheet countno = 4rowno =
4'DoIf
sheetcountno = totalsheets ThenExit SubElsesheetcountno = sheetcountno +
1rowno = rowno + 1 Sheets(sheetcountno).Select Cells(27, 2).Select
Selection.Copy Sheets("Index").Select Cells(1, 2).Value = rowno
Cells(2, 2).Value = sheetcountno Cells(rowno, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False Sheets(sheetcountno).Select
Cells(2,
15).Select Application.CutCopyMode = False Selection.Copy
Sheets("Index").Select Cells(rowno, 3).Select
Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False,
Transpose:=False Sheets(sheetcountno).Select Cells(2, 5).Select
Application.CutCopyMode = False Selection.Copy
Sheets("Index").Select
Cells(rowno, 4).Select Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
Sheets(sheetcountno).Select Cells(3, 5).Select
Application.CutCopyMode
= False Selection.Copy Sheets("Index").Select Cells(rowno,
5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks
_ :=False, Transpose:=False Sheets(sheetcountno).Select
Cells(3,
15).Select Application.CutCopyMode = False Selection.Copy
Sheets("Index").Select Cells(rowno, 6).Select
Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False,
Transpose:=False End If Loop'Application.EnableEvents = True
End
SubNow here's the problem, as soon as my manual code is run and gets to
the
line Sheets("Index").Selectit runs the Worksheet_Activate code and hence
the
(manual) code crashes when it can't paste the data to the sheet.Is there
some
way I can either combine these two macros (more elegantly hopefully) or
otherwise halt the running of the auto macro until I have successfully
run
the manual macro ?Your help ( as always) is greatly
appreciated.Regards,Neil