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.

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.