1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Worksheets: Make all sheets look at a specific cell

Discussion in 'VBA Macros' started by adamuce, Jul 12, 2018 at 10:21 AM.

  1. adamuce

    adamuce New Member

    Messages:
    23
    Hi Guys.

    I am working with an excel file with many worksheets all looking at different cells in each worksheet.

    Now I know if I select all worksheets together I can make all of them point to one cell, but is it possible that when I view each worksheet I dont need to scroll to find it?
  2. Logit

    Logit Active Member

    Messages:
    218
    You can paste this into the ThisWorkbook Module :

    Code (vb):
    Option Explicit


    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
         ActiveCell.Select
    End Sub
     
    adamuce likes this.
  3. adamuce

    adamuce New Member

    Messages:
    23
    Many thanks for that! But I dont know where to paste as I have no idea of Macros or VBA (which I am trying to learn as I posted in another section of this forum)
  4. vletm

    vletm Excel Ninja

    Messages:
    3,674
    adamuce
    Would this work with You?

    Attached Files:

    Thomas Kuriakose and adamuce like this.
  5. adamuce

    adamuce New Member

    Messages:
    23
    Many thanks for answering and providing this. I will try it!
    Just out of curiosity how do I make it to the cell I want?


    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

    Last edited by a moderator: Jul 13, 2018 at 1:20 PM
  6. vletm

    vletm Excel Ninja

    Messages:
    3,674
    adamuce
    ... Just out of curiosity how do I make it to the cell I want?
    hmm.. hmm.. It would work with almost any cell.
    Or ... What do You mean?
  7. adamuce

    adamuce New Member

    Messages:
    23
    The question was more of a way to understand how things worked really. For example if I could tune it to see perhaps a cell at the bottom of each sheet for example.
    Last edited by a moderator: Jul 13, 2018 at 3:21 PM
  8. vletm

    vletm Excel Ninja

    Messages:
    3,674
    adamuce
    There is always one cell active.
    After You select other sheet, that sheet's active cell will be same as well as it's position in window.
    If You select other cell and after that select other sheet, previous will happen again.
    Eg if You have now sheet1's cell F3 active ... You select any other sheet ... then there will be also same F3 cell active as well as it's position in window
    (If You have scrolled any row or column, those will take care too.)
    ... if You scroll 15 rows below and 20 columns right and select ... cell AA20
    and then You select other sheet .. that other sheet would take same 'scroll settings' as previous sheet and same AA20 cell will be active.
    >> test to select any cell and change to other sheet
    >> test to scroll and select any cell and change to other sheet
    adamuce likes this.
  9. adamuce

    adamuce New Member

    Messages:
    23
    Cheers for the explanation mate! You are a star.
    Last edited by a moderator: Jul 13, 2018 at 3:32 PM
  10. Logit

    Logit Active Member

    Messages:
    218
    Right click the Sheet1 tab, select VIEW CODE.

    In the top left, small window, DOUBLE CLICK - ThisWorkbook

    Paste the following into the large window on the right side :

    Code (vb):
    Option Explicit

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        ActiveCell.Select
    End Sub
    Save the workbook as any name you wish but it must be EXCEL MACO-ENABLED WORKBOOK.

    Close the workbook, re-open it. Now, whatever cell you select on multiple pages,
    is where the focus will jump to when the sheet is opened.

    Attached Files:

    Thomas Kuriakose and adamuce like this.
  11. adamuce

    adamuce New Member

    Messages:
    23
    Nice one mate. Will give it a try. I apologize for being difficult but I am on babyt steps coming into macros
    Last edited by a moderator: Jul 13, 2018 at 6:49 PM
  12. Logit

    Logit Active Member

    Messages:
    218
    No problem. We all start somewhere.
    adamuce likes this.

Share This Page