Microsoft Excel - I have a simple macro that won't obey my commands.

I have a workbook that I use to record various trouble tickets.  The first sheet is a snapshot of the relevant info, date, last entry etc., from each sheet, and I access each of the sheets via HTML hyperlink references.  On each individual sheet I have automated some things and the primary data entry cell is "O2", or more specifically a merged range of "O2:S6". 

My goal is to make "O2" the active cell every time I leave the sheet and go back to the snapshot/summary page so when I return to the page, I don't have to keep manually going back to O2.  So, on my macro button that returns me to the "Summary" page I had the simple macro "Sheets("Summary").Select".  I added "Range("O2:S6").Select" just prior to the "Sheets(Summary").Select" command so the O2 cell would be active just before returning to the Summary sheet.  I can see the O2 cell flash active just before I go to the summary sheet, but when I come back to the sheet later, the active cell is always "A1".

This is the Hyperlink reference, which has worked unaltered for months:


This is the macro that won't work.  Any idea why?  I also tried just "O2" versus "O2:S6" with no luck.

Sub Return_To_Summary()
'
' Return_To_Summary Macro
'

'
    Range("O2:S6").Select
    Sheets("Summary").Select
End Sub

Hello Troy,
Perhaps you need to specify the destination cell in your hyperlink function to go to the data page from the summary page.  If you don't specify the cell at that time it just defaults to A1 so your link location should look like "DataSheet1!O2".
Regards
Harry

Thank you Harry,

I think I understand what you're saying, but I'm not sure how to implement this.

My sheets are basically - Summary, 11, 22, 33, 44, 55.  Column B is populated via VB script with the sheet names, and used as a reference in the hyperlink in column A, with row 1 having headers.  How would I put in the reference you suggested? 

COLUMN A                                                      COLUMN B
(Row 2)=HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!A2",B2)            11
(Row 3)=HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!A3",B3)            22
(Row 4)=HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!A4",B4)            33
(Row 5)=HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!A5",B5)            44
(Row 6)=HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!A6",B6)            55

Hi Troy,
I would have thought
(Row 2)=HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!O2",B2)
but I will have a bit more of a poke around when I get time.
Regards
Harry

Hi Troy
(Row 2)=HYPERLINK("#"&B2&"!O2",B2)
seems to work.
Regards
Harry

Harry,

I apologize, I thought I'd responded.  Thank you very much for your reply.  I tried both options and the first option (HYPERLINK("[Ticket tracking.xlsm]'"&B2&"'!O2",B2)) worked best for my application.  When I tried the second option with the "#" in place of the workbook name it caused some problems, possibly because some of my sheet names have spaces in them? 

And my PC/OS/MS Office is having odd issues.  The day after I made these changes, the macro I had done making O2 the active cell started working again, although your option definitely works better.

Thanks again Harry!