79664269

Date: 2025-06-13 01:50:49
Score: 0.5
Natty:
Report link

DISCLAIMER: Please note that this code was written by an AI and is not running on Office 365 since I can't test on that. (You can tell by the comments)

I recall that we aren't supposed to post AI written code. But this is the answer that worked. This puts me in a situation where I'm not sure what to do. I'm not going to spend an hour or two rewriting it beyond what I've already done.

Option Explicit

Sub ScrollBothWindowsAfterNextTotal()
    Dim win1 As Window, win2 As Window
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nextTotal1 As Range, nextTotal2 As Range
    Dim startRow1 As Long, startRow2 As Long
    Dim currentWindow As Window

    ' Check if at least two windows are open
    If Application.Windows.Count < 2 Then
        MsgBox "You need at least two workbook windows open.", vbExclamation
        MsgBox "Current open windows: " & Application.Windows.Count, vbInformation
        Exit Sub
    End If

    ' Save current active window to restore afterward
    Set currentWindow = Application.ActiveWindow

    ' Define foreground and background windows
    Set win1 = Application.Windows(1) ' Active window
    Set win2 = Application.Windows(2) ' Background window

    ' --- Scroll Active Window (win1) ---
    Set ws1 = win1.ActiveSheet
    startRow1 = win1.ActiveCell.Row + 1

    ' Find the next "Total" in column C of active window's worksheet
    Set nextTotal1 = ws1.Columns("C").Find(What:="Total", After:=ws1.Cells(startRow1, 3), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not nextTotal1 Is Nothing Then
        ' Scroll active window to the row after "Total"
        win1.Activate ' Ensure active window is selected
        ws1.Cells(nextTotal1.Row + 1, 1).Select
        win1.ScrollRow = nextTotal1.Row + 1
    Else
        MsgBox "No 'Total' found in active window after row " & (startRow1 - 1), vbInformation
    End If

    ' --- Scroll Background Window (win2) ---
    Set ws2 = win2.ActiveSheet
    startRow2 = win2.ActiveCell.Row + 1

    ' Find the next "Total" in column C of background window's worksheet
    Set nextTotal2 = ws2.Columns("C").Find(What:="Total", After:=ws2.Cells(startRow2, 3), _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not nextTotal2 Is Nothing Then
        ' Activate background window temporarily to scroll it
        win2.Activate
        ws2.Cells(nextTotal2.Row + 1, 1).Select
        win2.ScrollRow = nextTotal2.Row + 1
    Else
        MsgBox "No 'Total' found in background window after row " & (startRow2 - 1), vbInformation
    End If

    ' Restore original active window
    currentWindow.Activate
End Sub

This code takes two open workbooks and scrolls to the next ' Total' in both windows... note that I didn't bother checking to make sure its the same name, that is intentional, since i might be missing data i want to see if the new version is missing it.

Reasons:
  • RegEx Blacklisted phrase (1): i want
  • Long answer (-1):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: zitot