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