Hi Les

Test this one

Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)

sh.Range("A1").Copy
With DestSh.Cells(Last + 1, "B")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

DestSh.Cells(Last + 1, "A").Value = sh.Name
'This will copy the sheet name in the A column if you want

End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Les" wrote in message
...
Hi Ron

Sorry I couldn't get the code to work for what I wanted it to do, probably
down to my inexperience.

I'm sure it's quite straightforward, I just need to copy the value of cell
A1 from each worksheet to compile a list in a new worksheet.

I would do it manually but there are more than 1000 worksheets.




"Ron de Bruin" wrote:

Hi Les

Try
http://www.rondebruin.nl/copy2.htm

But you can also create a link to each sheet
http://www.rondebruin.nl/summary.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Les" wrote in message
...
Can anyone help please.

I have a workbook that contains 1000+ worksheets believe it or not.
Not sure why or how it was done like that, but there it is.

I need a macro to copy the cell A1 from each sheet (it has the page
title
in
it) into a new sheet giving me a list of page titles.

Regards

Les.