• 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...

  • 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

A

adamuce

Member
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?
 
You can paste this into the ThisWorkbook Module :

Code: 
Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     ActiveCell.Select
End Sub
 
Logit said:
You can paste this into the ThisWorkbook Module :

Code: 
Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     ActiveCell.Select
End Sub
Click to expand...
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)
 
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:
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?
 
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:
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
 
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: 
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.
 

Attachments

  • Active Cell Select.xlsm
    11.2 KB · Views: 2
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:
You must log in or register to reply here.
Back
Top