Hi Donkin,

Try something like:

'==============
Public Sub Tester()
Dim WB1 As Workbook, WB2 As Workbook
Dim destSH As Worksheet
Dim SH As Worksheet
Dim destRng As Range

Set WB1 = Workbooks("Book1.xls") '<<===== CHANGE
Set WB2 = Workbooks("Book2.xls") '<<===== CHANGE
Set destSH = WB2.Sheets("Sheet1") '<<===== CHANGE

For Each SH In WB1.Worksheets
Set destRng = destSH.Cells(Rows.Count, "A").End(xlUp)(2)
SH.Range("A1").Copy Destination:=destRng
destRng(1, 2).Value = SH.Parent.Name & " " & SH.Name
Next SH
End Sub
'<<==============


---
Regards,
Norman


"Donkin" wrote in
message ...

What I am trying to do is copy Cell (a1) from each sheet in Book1 to a
list in Book2. The number of sheets in Book1 is variable and the sheet
names change regularly.

Sub Sheet_Select()
Windows("Book1.xls").Activate
Dim SH As Worksheet
For Each SH In ActiveWorkbook.Worksheets
Windows("book1.xls").Activate
Range("a1").Select
Selection.Copy
Windows("Book2.xls").Activate
Set W = Range("a1:a50").End(xlDown)
W.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next SH

End Sub

This will select Cell A1 in book1 and copy to a list in Book2
unfortunately this repeats for the number of sheets in Book1 but does
not cycle through the sheets.


--
Donkin
------------------------------------------------------------------------
Donkin's Profile:
http://www.excelforum.com/member.php...o&userid=23715
View this thread: http://www.excelforum.com/showthread...hreadid=482732