Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help with macro (Excel 2000)

    HI!

    oK... Here we go...

    I have a user who has two workbooks. One has summary data the other has a worksheet with details regarding the summary data. (100+ worksheets) He's doing a presentation Monday with this data and he will be flipping back and forth between the summary and detail workbooks.

    Is there any way to create a button on the summary sheet where a macro will switch to the detail workbook and go to the sheet tab with the appropriate info. The detail sheet tabs are named with the product numbers that coorespond to the product number in the 'product column' on the summary worksheet and the product number exists on the detail worksheets as well.

    Any ideas would be helpful as I have 0 time to research this... we are rolling out XP in a week!

    Thanks All!
    <img src=/S/please.gif border=0 alt=please width=31 height=23>

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Location
    Long Beach, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with macro (Excel 2000)

    You might try a macro like the following that responds to a double-click:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    On Error Resume Next
    Sheets(ActiveCell.Text).Select
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need Help with macro (Excel 2000)

    Have you tried Using Hyperlinks?

    You could a hyperlink on the summary sheet to goto the other workbook and a particular sheet.

    You could use the hyperlink formula:

    If D1 eg had the sheet name: eg SheetName
    =HYPERLINK("[Filename.xls]"&D1&"!A1")

    You could also create a hyperlink BACK to the summary sheet. and place it on each sheet or make a button on the toolbar.


    Steve
    [Book2.xls]Sheet1!$A$1

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help with macro (Excel 2000)

    Hi!

    Thanks guys! I did suggest the hyperlink thing to him but with over 400 lines on the summary and 100+ worksheets he wasn't to keen on doing that.

    I will try the macro first thing in the morning with him and let you know how it goes.

    I really appreciate the input... I just don't have the couple of hours it would''ve taken me to work through this... XP rollout next week and way too much left to do... wish me luck!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Need Help with macro (Excel 2000)

    If the cell name can easily be made into the hyperlink formula, it is just a copy down a column of names. If not direclty convertable then you could have hyperlink formula combined with a lookup table.

    The good thing with hyperlink formula is it could be copied easily down 400 lines of the summary. It also requires no VB coding.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •