Want to cause multi worksheets to move to same place, based on content not cell address

[NCkermel]

I am an admin for a sales department. I maintain an Excel workbook, updated daily, related to the submitted sales orders. It includes four worksheets, one for each manager. Each worksheet is then broken down by week, then sales rep. Each sales rep starts the month with four blank lines for sales orders, under each week. (See left side of example picture.) Row 1 has headers for the columns and each worksheet is frozen so that Row 1 is always visible. The hidden rows are placeholders for future new hires.

Also in this workbook:

There is one Admin sheet, where I stash source data (like sales rep names and fiscal dates) for formulas in the manager sheets.

There are also a couple of sheets that compile data from the manager sheets for an overall tally. Those sheets aren’t relevant to this question except to point out that there are a few sheets that I don’t want a possible solution to apply to.​

I want to be able to run something so that when I click a "Week 2" button on my Admin sheet, all of the manager worksheets adjust to have the first reference to “Wk2” in the second visible row. Ditto for Weeks 1, 3, 4 and 5. (See right side of example picture.)

Complication:

I sometimes have to add rows when a sales rep has more than four sales orders in a week. So the month may begin with “Wk2” in row 64 for every manager’s sheet, but they rarely finish the month that way. It could be in row 64 for two managers, row 70 for the third, row 73 for the fourth. And that, of course, rolls over to Weeks 3-5.​

My first effort was creating a macro based on scrolling to the necessary cells. For example, for the Week 2 macro, I recorded going to each worksheet, scrolling so that the Wk2 row was positioned as the second viewing row. But when I add rows (as mentioned in “Complication,” above), it seems to throw that scrolling function off. But if nothing else works, I can live with this – the scrolling may not get me right where I want, but it gets me close-ish.

I found a VBA code to put the cursor in A1 in each worksheet. (https://www.extendoffice.com/documents/excel/4015-excel-always-open-at-cell-a1.html Apologies that this isn’t a URL from this forum – I’ve done a lot of clicking and searching lately and that’s one I tried on the day I started writing this question.) I successfully added that code, but it doesn’t do quite what I want. I have tried naming the cells and adapting the “always open at cell a1” VBA code to look for the week’s names (“Wk1,” etc.) instead of a cell location (A130), but that doesn’t seem to work how I want it to, either.

I can almost always teach myself how to do something via Google or YouTube results, but I’m striking out with this. What I want to do may not be possible, or I may be fumbling with my search terms. (And if the latter is true, then I certainly apologize for posting this question here, but I really did try to find it before I registered to post.)

Maybe I’m overthinking it and there’s an easier solution I’m missing. But if it is possible, and if anyone has a suggestion, I’d be very grateful.

I am using Excel 2010 in Windows 7.

This is my first post here and I believe I have followed the rules and provided the information needed, but please let me know if I can clarify anything.

 

[NCkermel]

It's been almost two weeks since I posted my question and I don't have any replies yet (and not a lot of views), so I'm bumping for visibility.

If no responses after this, I'll presume what I'd like to do can't actually be done and I won't bump again.

Thanks in advance for taking the time to read my post.

 

[Fluff]

What are the names of the 4 sheets you're interested in & what is the name of the Admin sheet?

 

[Fluff]

Hi & welcome to the board
This should do what you require.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim Sht As Worksheet
    Cancel = True

Application.ScreenUpdating = False
    For Each Sht In Sheets(Array("[COLOR=#ff0000]Details[/COLOR]", "[COLOR=#ff0000]View[/COLOR]", "[COLOR=#ff0000]Task[/COLOR]", "[COLOR=#ff0000]Pick[/COLOR]"))
    Sht.Activate
        Sht.Columns(1).Find(What:=Target.Value, After:=Sht.Range("A1"), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=True).Activate
    ActiveWindow.ScrollRow = ActiveCell.Row
    Next Sht
    Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Activate

End Sub

To install this code, right click on the Admin Sheet tab, select View Code & paste the above into the code window that appears.
Then change the parts in red to match the sheet names of your mangers.
Change the part in blue to match the name of the Admin Sheet.
Then double click on Wk2 in col A of your admin sheet & check the managers sheets.