Excel VBA switching between workbooks

Hi, Would you be able to advise why this code does not work:

Sub Hyperlinks()
''Set up hyperlinks
Workbooks.Open ("I:\DATA\Enterprise\Peugeot EQC\PSA Bimarques\Project\Peugeot Bi-marque Final 10Mar10.xls")
BimarquesFinal = ActiveWorkbook.Name
Sheets(1).Select

For y = 3 To 57

Dim HyperAddress As String
HyperAddress = "'" & ThisWorkbook.Sheets("Contents list").Cells(y, 2).Value & "'!A1"
Workbooks(BimarquesFinal).Sheets(2).Cells(y, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
HyperAddress, TextToDisplay:= _
Workbooks(BimarquesFinal).Sheets("Workbook Contents").Cells(y, 1).Value

Next y

The line which does not work is:

Workbooks(BimarquesFinal).Sheets(2).Cells(y, 1).Select

Basically, all I want to do is, once I've picked up my HyperAddress values from the workbook which contains the VBA, go back to the workbook which I've just opened and select a cell on sheet 2! I've tried a number of things and can't seem to find a way of specifying this without it falling over.

Thanks

Tracey

RE: Excel VBA switching between workbooks

Hi Stephen,

Yes, it's run-time error 1004 - "Select method of Range class failed".

Tracey

RE: Excel VBA switching between workbooks

Thanks. So, for future reference, you cannot select a cell without first having selected the worksheet? You cannot select both a worksheet and a cell with the same line of code?

Could I also have achieved the same result by selecting the worksheet on one line and the cell on the next line?

Tracey

---

Sub Hyperlinks()
''Set up hyperlinks
Workbooks.Open ("I:\DATA\Enterprise\Peugeot EQC\PSA Bimarques\Project\Peugeot Bi-marque Final 10Mar10.xls")
BimarquesFinal = ActiveWorkbook.Name

For y = 3 To 57

Dim HyperAddress As String
HyperAddress = "'" & ThisWorkbook.Sheets("Contents list").Cells(y, 2).Value & "'!A1"
Workbooks(BimarquesFinal).Sheets(2).Select
Cells(y, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
HyperAddress, TextToDisplay:= _
Workbooks(BimarquesFinal).Sheets("Workbook Contents").Cells(y, 1).Value

Next y