Tracey has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
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 Tracey
Thanks for your question
Could you clarify something for me? When your code falls over does it generate a run time error. If so what is the number and description?
Thanks
Stephen
RE: Excel VBA switching between workbooks
Hi Tracy
Thanks for the update. Sorry about the delay in getting back to you, I've been down with flu.
Your problem is that you can only select a cell in the active worksheet. In your code, before the loop you select sheet 1 and then in the loop you try to select a cell in sheet 2.
If you select sheet 2 before the loop that should solve the problem
Regards
Stephen
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
